Is there a way to edit caption on Form Control buttons?

button, excel, vba

Solution

Well, right click the Sheet1 tab on the bottom of spreadsheet and select view code

Copy paste the following code

Private Sub Worksheet_Activate()
    Buttons("Button 1").Caption = "NEW TEXT"
End Sub

This event fires up when Sheet1 gets activated and it changes the caption on the button to `NEW TEXT`

Obviously you can refer to your buttons either by name or their index. You would to modify the `"Button 1"` if you want to modify the text on a different object.

You can assign it a variable or evaluation of an expression if you replace the `"NEW TEXT"`

For example

if on `Sheet2` you type anything in the cell A1 you can modify the code behind Sheet1 and use

Private Sub Worksheet_Activate()
    Buttons("Button 1").Caption = Sheets("Sheet2").Range("A1")
End Sub

this way every time you activate Sheet1 the text on a button will be picked up from Sheet1, Cell A1

To find out the name of the button look here

Problem

Is it possible (on a sheet change event), to change the caption text of a Form Control button? Ideally, I would like to add a variable into the existing button caption depending on other data in the workbook.

Original source