View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default 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