CODEWITHSUNDEEP
excelexcel-formulavba
Tom Ruiz
Tom Ruiz

Reputation: 307

Text displayed on a button Excel Macro

How do or is it possible to have a button assigned to a Macro and have the text of such button be a formula?

i.e

If A2 is "John Doe"

Can the button have John Doe as the text??? and If I change the name to "Jane Doe" have a formula (=A2) so that when Ichange the text the display text of the button change automatically???

Upvotes: 2

Views: 5171

Answers (1)

Regiz
Regiz

Reputation: 467

The same has been answered through comments. However it has been marked as unanswered, hence answering behalf of PKatona

Answer copied from

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

Answer 1

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

enter image description here

Upvotes: 1

Related Questions