Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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" wrote: 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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 "John Baker" wrote in message ... 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" wrote: 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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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. -- Regards, Tom Ogilvy "John Baker" wrote in message ... 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" wrote: 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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Button Name based on Data? Can it be done?
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. -- Regards, Tom Ogilvy "John Baker" wrote in message ... 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help creating a sum based on variable data. | New Users to Excel | |||
can excel insert rown based on variable data in a cell? | Excel Worksheet Functions | |||
How to import data based on a variable | Excel Worksheet Functions | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
Getting data from another workbook based on variable | Excel Discussion (Misc queries) |