Prints out all macro names which are assigned to any menu button
Sub ReadBack_Buttons()
On Error Resume Next
'## Loop through every menu bar
For Each bar In Application.CommandBars
'## Loop through every button on the current menu bar
For Each button In bar.Controls
'## If a macro is assigned, print it out
If button.OnAction <> "" Then Debug.Print button.Caption & " = " & button.OnAction
'## Loop through every button on dropdown menus
For Each subbutton In button.Controls
'## If a macro is assigned, print it out
If subbutton.OnAction <> "" Then Debug.Print subbutton.Caption & " = " & subbutton.OnAction
Next
Next
Next
End Sub
For a quick test, add your own custom menu with this second macro.
Sub CreateCommandBar()
On Error Resume Next
'## Delete the commandbar if it exists
Application.CommandBars("example").Delete
'## Create a new Command Bar
Set bar = CommandBars.Add(Name:="example", Position:=msoBarFloating)
bar.Visible = True
'## Add popup menu
Set menu1 = bar.Controls.Add(Type:=msoControlPopup)
menu1.Caption = "My custom menu"
'## Add button 1 to popup menu
Set Btn2 = menu1.Controls.Add(Type:=msoControlButton)
Btn2.Caption = "missing macro assigned"
Btn2.OnAction = "Not_working_dummy"
'## Add button 2 to popup menu
Set Btn2 = menu1.Controls.Add(Type:=msoControlButton)
Btn2.Caption = "Hello World"
Btn2.OnAction = "Hello_world"
End Sub
Sub Hello_world()
MsgBox "Hey, it works"
End Sub
After you've executed CreateCommandBar
you will see a new entry on your main menu. One with a working macro assigned and one without
Now, run the first macro ReadBack_Buttons
and have a look at the immediate pane
missing macro assigned = Not_working_dummy
Hello World = Hello_world
Hmmm... Does this method also work if the toolbar (
CommandBar
) was made at design time? (I mean: Using the toolbar designer tool in Word 2003?) I have full access to the entire VBA code in the Word 2003 template. Unfortunately there is nothing likeCommandBars(index).Controls.xxx
– Hauns TM – 2014-03-10T20:51:47.743@HaunsTM Oh sorry, I confused Excel and Word. Fixed it and worked out a better solution. Hope this helps – nixda – 2014-03-11T02:59:45.657