Re-formulating the problem: Dynamic Button Names!
 

Hi,

I posted this before but it doesn't seem I put it right so I apologize.
Will try to make better this time!

In cells d1,e1, and f1 I have 3 buttons. Each is assigned to a macro
called Sys1, Sys2, Sys3.

If I click on Tools-Macro-Macros under "Macro name: Sys1", and then
click on "Edit" I have the sub code behinde Sys1 as follows:

Sub Sys1()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem1").Select
Selection.EntireRow.Hidden = True
End Sub
---------------------------------------------
Sub Sys2()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem2").Select
Selection.EntireRow.Hidden = True
End Sub
-------------------------------------------
Sub Sys3()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem3").Select
Selection.EntireRow.Hidden = True
End Sub

Sub Sys1, Sub Sys2, and Sub Sys3 codes do exist in "Module1" next to
each other as shown above.

Currently, I use "Edit Text" to change a button name. But want their
names to change automatically eveytime I change the contents of, say,
cells a1, a2, a3.

I know it is a simple thing but have not done before.

Thanks for help,
Mike

Re-formulating the problem: Dynamic Button Names!
 

The code is the same:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
Me.Buttons(1).Caption = Range("A1").Value
Me.Buttons(2).Caption = Range("A2").Value
Me.Buttons(3).Caption = Range("A3").Value
End If
End Sub




Buttons are not "in cells" They may be placed over cells, but knowing that
doesn't provide much information.

Do you need me to send you a sample workbook so you can see how this is
done?

--
Regards,
Tom Ogilvy




"Mike" wrote in message
ups.com...
Hi,

I posted this before but it doesn't seem I put it right so I apologize.
Will try to make better this time!

In cells d1,e1, and f1 I have 3 buttons. Each is assigned to a macro
called Sys1, Sys2, Sys3.

If I click on Tools-Macro-Macros under "Macro name: Sys1", and then
click on "Edit" I have the sub code behinde Sys1 as follows:

Sub Sys1()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem1").Select
Selection.EntireRow.Hidden = True
End Sub
---------------------------------------------
Sub Sys2()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem2").Select
Selection.EntireRow.Hidden = True
End Sub
-------------------------------------------
Sub Sys3()
Range("System").Select
Selection.EntireRow.Hidden = False
Range("Subsystem3").Select
Selection.EntireRow.Hidden = True
End Sub

Sub Sys1, Sub Sys2, and Sub Sys3 codes do exist in "Module1" next to
each other as shown above.

Currently, I use "Edit Text" to change a button name. But want their
names to change automatically eveytime I change the contents of, say,
cells a1, a2, a3.

I know it is a simple thing but have not done before.

Thanks for help,
Mike

Re-formulating the problem: Dynamic Button Names!
 

Tom,

Thanks alot indeed. It is working now. Because I have alot of buttons,
in different sheets, that I want to make their names dynamic I have 2
question:

1)Can you say .Range("List") instead of .Rang("c1:c8"); where "List is
a range name?
2)Suppose you are in Sheet2, can you say .Range("Sheet1!List") instead
of .Rang("Sheet1!c1:Sheet1!c8")?

Thanks,
Mike

Re-formulating the problem: Dynamic Button Names!
 

in a general module, Range("List") should work in all cases. In a sheet
module, if the list range is not on that sheet
worksheets("Sheet1").Range("List")

--
Regards,
Tom Ogilvy

"Mike" wrote in message
oups.com...
Tom,

Thanks alot indeed. It is working now. Because I have alot of buttons,
in different sheets, that I want to make their names dynamic I have 2
question:

1)Can you say .Range("List") instead of .Rang("c1:c8"); where "List is
a range name?
2)Suppose you are in Sheet2, can you say .Range("Sheet1!List") instead
of .Rang("Sheet1!c1:Sheet1!c8")?

Thanks,
Mike