Dynamic Button Name?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi everone,

Can a button name be made dynamic?

For example, you create a button and want its name be read from cell
A1. So if A1 read "Mike", the button name automatically become "Mike"
and so on.

Thanks,
Mike
 
for a button from the Forms toolbar, you can use this event macro. Put
it in the worksheet code module (right-click the worksheet tab and
choose View Code")

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
 
You can put following code in your worksheet.
Here, put the correct button name!
===================================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
commandbutton1.Caption = Target.Text
End If
End Sub
===================================================
Hope this helps.

~Yogendra
 
Thank you JE and Yogendra.

However, I already have a macro under that button like this:

Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub

How this would become after I add your "Private Sub" to it?

Mike
 
Mike

JE's code would be in addition to the code you have assigned to the button.

The code is event code and is fired when a change is made in A1 and will
change the Button Nmae to whatever you entered in A1.

Don't touch your existing code.

Right-click on the worksheet tab and "View Code".

Copy/paste JE's code into that module.


Gord Dibben Excel MVP
 
I tried this but not working! Let me explain one thing please. I have 4
buttons, each is assigned to a sub. The 4 subs are in one module. Each
button is assigned to one sub.

Two questions: take button #1
1)It is already assigned to sub #1, how would I assign it to JE's sub?
2)How would I put sub #1 and JE's sub togother? Next to each other like
this:

Sub UnhideSubsystem1()
Range("System").Select
Selection.EntireRow.Hidden = False
End Sub

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

Thanks,
Mike
 
JE's code wouldn't be assigned to the button. It's the worksheet event that
actually adjusts the caption of the button (from the Forms toolbar).

And my question: Don't you want the button to run the same sub--no matter what
the caption is? If that's true, you shouldn't need to reassign any macro to
that button.

On the other hand, if you want to run different code based on the caption, then
you'll have to share those details.

ps.

Read JE's post once more. You'll see that his code doesn't go into a general
module (like your 4 subroutines). It goes behind the worksheet that should have
that "recaption the button" behavior.
 
If I to assign JE code to the worksheet, and since I need to make each
of the 4 buttons have a dynamic name read from a different cell, how
the worksheet would recognize that?

Again, I need to make each button has a dynamic name read from a cell.
So, how JE code here would be used?

Mike
 
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1, B2:B3, D4")) Is Nothing Then
Me.Buttons(1).Caption = Range("A1").Text
Me.Buttons(2).Caption = Range("B2").Text
Me.Buttons(3).Caption = Range("B3").Text
Me.Buttons(4).Caption = Range("D4").Text
End If
End Sub
 
Back
Top