Imprime todos os nomes de macros atribuídos a qualquer botão de menu
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
Para um teste rápido, adicione seu próprio menu personalizado com esta segunda 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
Depois de executar CreateCommandBar
, você verá uma nova entrada no menu principal. Um com uma macro de trabalho atribuída e outra sem
Agora, execute a primeira macro ReadBack_Buttons
e dê uma olhada no painel imediato
missing macro assigned = Not_working_dummy
Hello World = Hello_world