Dynamic Button Names?
 

Hi,

Say you have 3 buttons, each has its sub, and all 3 subs code are
listed in one module.

I want the button names to be read from 3 different cells. So everytime
a cell content change, the name of its related button name change
automatically.

I have the code for the 3 subs as follows:

Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Now, the sub code for the button name change is:

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

My question:
Can I assign a button to 2 subs at the same time? Or, how can I
incorporate the "Private Sub" into each of the other 3 subs, so I end
up with 3 subs?

Thanks,
Mike

Dynamic Button Names?
 

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

--
Regards,
Tom Ogilvy


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

Say you have 3 buttons, each has its sub, and all 3 subs code are
listed in one module.

I want the button names to be read from 3 different cells. So everytime
a cell content change, the name of its related button name change
automatically.

I have the code for the 3 subs as follows:

Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub
------------------------------------------
Now, the sub code for the button name change is:

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

My question:
Can I assign a button to 2 subs at the same time? Or, how can I
incorporate the "Private Sub" into each of the other 3 subs, so I end
up with 3 subs?

Thanks,
Mike

Dynamic Button Names?
 

Tom,

One thing I don't understand. If I have 5 buttons in one sheet, how
does your code understand which button I am refering to?

Thanks,
Mike

Dynamic Button Names?
 

Your original posting doesn't make much sense. You list 3 macros that are
identical. so they could not exist in the same module without getting
errors.

in any event you said:

I want the button names to be read from 3 different cells. So everytime
a cell content change, the name of its related button name change
automatically.

So we are reacting to a change in cell content - not a button being pushed.

the code posted has the definition of the relation between the cell and the
button. I simplified it so it updates the button names on all buttons if
any of the 3 related cells change. So it is a little stupid and does extra
work, but achieves the desired result.

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

so Button(1) gets the name/caption found in A1
Button(2) gets the name/caption found in A2
Button(3) gets the name/caption found in A3

--
Regards,
Tom Ogilvy


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

One thing I don't understand. If I have 5 buttons in one sheet, how
does your code understand which button I am refering to?

Thanks,
Mike