Excel VBA - change text of CommandButton

Question

I don't understand why this VBA code returns run-time error 438:

        With Worksheets("DATA1").Shapes("CommandButton4")
            .TextFrame.Characters.Text = "In Stock"
        End With

I need to change the caption of the button.

The code is in Private Sub Workbook_Open().

What type of a Control (Button) are you talking about? Is it a Frame or an ActiveX control? — FaneDuru
– FaneDuru, Commented Jan 15, 2022 at 15:14

FaneDuru · Accepted Answer · 2022-01-15 15:30:47Z

2

I asked you about the button type, but no answer. So, I will provide solutions for both types (Form or ActiveX). Please, check the next pieces of code:

In case of an ActiveX button (more probable, according to its name):

Sub changeActiveXButtonCaption()
   Dim bt As MSForms.CommandButton
   Set bt = ActiveSheet.OLEObjects("CommandButton4").Object
   bt.Caption = "In Stock"
End Sub

In case of a Form button, please check/use the next code:

Sub changeFormButtonCaption()
   Dim bt As Button
   Set bt = ActiveSheet.Buttons("CommandButton4")
   bt.Caption = "In Stock"
End Sub

Any of the above controls do not have a TextFrame property. Such a property, usually, belongs to a shape. Is your, so named, "CommandButton4" button a shape?

edited Jan 15, 2022 at 15:30

answered Jan 15, 2022 at 15:25

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dolphin975 Over a year ago

Sorry, I was out for some errands. I tried your first solution and it works great. Thank you very much for the awesome help. Really appreciated FaneDuru!

FaneDuru Over a year ago

@Dolphin975 This means that the button in discussion is of ActiveX type...

Collectives™ on Stack Overflow

Excel VBA - change text of CommandButton

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related