Can I display the macro name on the command button?

[JenniferMurphy]

Is there a way to have the name of macro that is assigned to a command button be displayed on that button? Then if I change the macro that is assigned, the text oin the button will automatically change.

I have a sheet with several command buttons doing different things. Sometimes I get mixed up or distracted and have the wrong macro assigned to a button that has text saying it is doing something else.

Thanks

 

[Misca]

Try something like

Sub UpDateButtons()

Dim BT As Shape
Dim SubName As String
Dim TxtLen As Integer

For Each BT In ActiveSheet.Shapes
    With BT
        SubName = .OnAction 'The whole sub name including workbook name
        TxtLen = Len(SubName) - InStr(SubName, "!") 'The length of the actual sub name
        
        
        If SubName <> "" Then
           .TextFrame.Characters.Text = Right(SubName, TxtLen)
        End If
    
    End With
    
    SubName = ""
    
Next BT

End Sub

 

[JenniferMurphy]

Try something like

Sub UpDateButtons()

Dim BT As Shape
Dim SubName As String
Dim TxtLen As Integer

For Each BT In ActiveSheet.Shapes
    With BT
        SubName = .OnAction 'The whole sub name including workbook name
        TxtLen = Len(SubName) - InStr(SubName, "!") 'The length of the actual sub name
       
       
        If SubName <> "" Then
           .TextFrame.Characters.Text = Right(SubName, TxtLen)
        End If
   
    End With
   
    SubName = ""
   
Next BT

End Sub

Thanks, I'll give that a try.

So I take it there is no property that I can assign to the button or shape that will automatically display the macro name.

I was hoping for something like this as the text:

Update the averages (&MacroName)

which would display as:

Update the averages (UpdateAverage)

If it displayed as

Update the averages (DiffCheck)

I would know at a glance that it was the wrong macro.

In the meantime, can you modify your code to just do the control that called it, rather than all of the controls on that sheet? That way I can put that code in each macro to check if it has the right text before running it.

Thanks

 

[My Aswer Is This]

Here is a script I use to Make a shape button which puts the script name in the button Caption.
So if my script is name "Hello" it puts "Hello" in the Shapes Caption.

Then assign the script to this button.
So if script is name "People" then Put People in the Input Box

I activate this script by using a Shortcut key and have the script in my Excel Personal folder so I can run script no matter what workbook I have Open.

Sub Make_Me_A_Button()
'Modified  11/9/2021  12:38:49 AM  EST
MyValue = Application.Proper(InputBox("Enter The Name Of Your Macro"))
aa = MyValue
aaa = Replace(aa, " ", "_")
MyValue = MyValue
ans = ActiveCell.Address
ActiveSheet.Shapes.AddShape(msoShapeRectangle, Selection.Left, Selection.Top, Selection.Width, Selection.Height).Select

    With Selection.ShapeRange.Shadow
        .Type = msoShadow25
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 5
        .OffsetX = 4.9497474683
        .OffsetY = 4.9497474683
        .RotateWithShape = msoFalse
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Size = 100
    End With
   
    With Selection.ShapeRange.TextFrame2
        .TextRange.Characters.Text = MyValue
    End With
   
    With Selection
        .Placement = xlFreeFloating
        .Font.Size = 16
        .ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .ShapeRange.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
        .ShapeRange.TextFrame2.WordWrap = msoFalse
    End With

Range(ans).Offset(5, 0).Select
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 573, 98.25, 171.75 _
        , 61.5).Select
   Selection.Delete

End Sub

 

[RoryA]

Rather than the For Each BT in Misca's code, you could just use:

Set BT = Activesheet.Shapes(application.Caller)