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

  • Thread starter Thread starter John Baker
  • Start date Start date
J

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
 
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
 
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
 
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
 
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.
 
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
 
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.
 
Back
Top