How do I find out or change programmatically which macro is attached to a button on my toolbar?

Article contributed by Astrid Zeelenberg

Sub ShowAndModifyMacroNames()

Dim oToolbar As CommandBar
Dim oButton As CommandBarControl
Dim sMacroName As String

On Error Resume Next

'Replace the name if you've used another toolbar
Set oToolbar = CommandBars("Standard")

For Each oButton In oToolbar.Controls

    'Check if it is a customized button
 
  If Not oButton.Builtin Then

        'Ask for the macroname for the button
   
    sMacroName = InputBox(prompt:="Enter macroname", _
                Title:="Macros on toolbar buttons", _
                Default:=oButton.OnAction)

        If Trim(sMacroName) <> 0 Then
            'Set the macro for the button
            oButton.OnAction = sMacroName
        End If

    End If

Next

Set oButton = Nothing
Set oToolbar = Nothing

End Sub


Click to view Terms of Use page

Click to view Disclaimer page