VBA - How to change the button text

Question

I assigned an Excel VBA macro to a command button, and I am needing to know how to update the text to "Loading" when the macro is executed.

I have tested the following code with no success:

Worksheets("Sheet1").Button9.Caption = "Loading"

Trainband

What doesn't work? Whats the error message? – Addi

Check the name of the button and the name of the worksheet – Halle

Answer 1

Go via the Buttons collection:

Worksheets("Sheet1").Buttons("Button9").Text = "Loading"

Elvera

i used .Caption = "Loading..." instead of .Text = "Loading" and it worked – Trainband

Answer 2

You should try using an Active X button and add this code to that active X button click event. Also, put this code in the same worksheet where this active X button is.:

I am assuming your button starting text is "Original Text". You can replace it with your own text in the code. Also, I am adding 5 seconds delay just to mimic that something is loading. you can replace that part with your code. Also note that in my case active X button name is CommandButton21, in your case it may be different. Update your code accordingly.

Private Sub CommandButton21_Click()
CommandButton21.Caption = "Loading"
Application.Wait (Now + TimeValue("0:00:05"))
CommandButton21.Caption = "Original Text"
End Sub

Borrego

Answer 3

This worked for me

Worksheets("Sheet1").Button9.TextFrame.Characters.Text = "Loading"

Selflove

Answer 4

If it is an Active X button:

Dim ws As Worksheets
Set ws = Worksheets("Sheet1")

Worksheets(ws.Name).addbutton.Caption = "Ajouter"

Where addbutton is the button name.

Unwelcome

Answer 5

Go via the Shapes collection:

Worksheets("Sheet1").Shapes("Button9").AlternativeText = "Loading"

(Not every button/shape has TextFrame.Characters.Text)

Gallnut

Answer 6

Assuming the button is on Sheet1, and the name of the Button is "Button 116", you can get or change the text of the button as follows:

Sheet1.Buttons("Button 116").Text = "New Button Text"

s1 = Sheet1.Buttons("Button 116").text

If you want to refer to the specific Sheet by the Name of the sheet rather than the physical sheet order, you can use:

Worksheets("Sheet Name").

If you don't know the name of the sheet (e.g. buttons from different sheets use the same macro), you can use:

ActiveSheet.

Form Buttons are tricky because the default generated name of the button contains a space (e.g. "Button 116"). The easiest way to refer to the button is by that name with quotes. You can find the name of the Button by selecting it, and then looking in the upper left hand corner of the sheet where it shows the name of the selected object (make sure you have the "Formula Bar" checked under the View Menu).

It's frustrating if you are used to VBA automatically showing you the available options as you type further into a reference. For example, if you enter:

ActiveSheet.

VBA doesn't list the available options (at least, not in my version of Excel - Office 2016 Pro). If you Type the .Button reference it will accept it and capitalize it after you hit enter.

If you Type:

Sheet1.

VBA will give you a list of options, but "Buttons" is not in the list of options - even though Buttons is a valid option (again, on my Office 2016 Pro version of Excel).

Insensitive