Variable Button Name based on Data? Can it be done?

[John Baker]

Hi

I am afraid I have been asking a lot of questions here as I develop my application. I hope
the results are useful to others too.

I have push buttons on my spreadsheet, and would very much like to be able to change the
name on them as conditions on the spreadsheet change. For example, we have some fields and
functions that are linked to the month and day (a variable based on other conditions). I
would like to be able to change button text so that the month and day that appear on the
button (although the actual macro linked to the button will not change). The data I would
like to show in the button resides in cells in the spreadsheet, and is being used for
other things as well.

My problem is that when I edit the button contents, all I get it the hard data I enter.
Playing around with the name in properties does not get me far either. I don't even know
if there is a way to do it, but if there is can someone share it with me please?

Regards

John Baker

 

[Chip Pearson]

John,

I assume you are using the Command Buttons from the Controls toolbar, not
the Forms toolbar. Given that, you can change the Caption property of the
button to the new text. E,g.,

Sheet1.CommandButton1.Caption = "New Text"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[John Baker]

Chip:

Either that doesn't work for me, or Iam too much of a newby to understand your suggestion.
I set up a macro with the line you gave me, changing the names to my sheet name and
command button, and making the source of the name a variable containing the name I
wanted..Thus:

Sub test()
'
' test Macro
' Macro recorded 11/21/2003 by John H Baker
Sheets("datefunction").Select
Range("f2").Select
ActiveCell() = but6
Sheets("Input").Select
Sheets("input").CommandButton6.Caption = but6
'
End Sub

The only result appears to be to blank out the data in datefunction!F2!

Can you give me just a tad more guidance?

Regards

John Baker

 

[Chip Pearson]

John,

Try something like the following:

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
WS.OLEObjects("CommandButton1").Object.Caption = "New Text"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

you are using but6 as a variable - and since it has not been set to a value,
it is blank - should blank the caption of the button as well.


Sub test()
'
' test Macro
' Macro recorded 11/21/2003 by John H Baker
but6 = Format(Date, "yyyy_mm_dd")
Sheets("datefunction").Select
Range("f2").Select
ActiveCell() = but6
Sheets("Input").Select
Sheets("input").CommandButton6.Caption = but6
'
End Sub

worked fine for me.

 

[John Baker]

Thanks..that does work.

Now, is there any way to activate that macro when the contents of a fiield changes. For
example, when the month changes in a field?

Regards

John

 

[Tom Ogilvy]

How does the month change? If by calculation, use the calculate event. If
by editing, then use the change event. In either case, call your macro.