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