I had occasion to mess with the right click menu you get with a checkbox. It’s the Excel Object menu, but I can’t ever remember that. I opened my old “find the right click menu” file and found it the old fashioned way. Then I blogged about it.
As I mentioned in the post, it later dawned on me that I was making that whole process too complicated. I don’t know why mind works so inefficiently, but it does and that’s my cross to bear. While I was thinking how to package all that class module complexity into a nice add-in, I thought “Why don’t I just add a control to every freakin’ popup menu with the menu’s name on it?” I slapped my forehead so hard I woke the whole house.
Sub RightClickSpy()
Dim cBar As CommandBar
Const sTAG As String = “RightClickSpy”
For Each cBar In Application.CommandBars
If cBar.Type = msoBarTypePopup Then
With cBar.Controls.Add(msoControlButton, , , 1, True)
.Caption = .Parent.Name
.Tag = sTAG
End With
End If
Next cBar
End Sub
Sub UndoRightClickSpy()
Dim ctl As CommandBarControl
Dim ctlAll As CommandBarControls
Const sTAG As String = “RightClickSpy”
Set ctlAll = Application.CommandBars.FindControls(, , sTAG)
For Each ctl In ctlAll
ctl.Delete
Next ctl
End Sub
 |
 |
 |
Dick,
I liked this problem. I came up with something along the same lines but not so simple. It prints out all the controls’ IDs as well. I’ve gotten burned by referring to controls by their captions – the captions sometimes change names slightly, e.g., “Column Width…” becomes “Width…” I think that IDs stay the same:
Sub add_popup_names_button()
Dim cbar As CommandBar
Dim cbutton As CommandBarButton
For Each cbar In Application.CommandBars
With cbar
If .Type = msoBarTypePopup Then
Set cbutton = .Controls.Add(temporary:=True)
With cbutton
.Caption = “Show popup info”
.Style = msoButtonIconAndCaption
.FaceId = 284
.OnAction = “show_popup_info”
End With
End If
End With
Next cbar
End Sub
Sub show_popup_info()
Dim i As Integer
With CommandBars.ActionControl.Parent
Debug.Print “Menu = ” & .Name & String(7, ” “) & “Index = ” & .Index & vbLf
Debug.Print “Caption” & String(17, ” “) & “ID”
For i = 1 To .Controls.Count – 1
With .Controls(i)
Debug.Print ” ” & .Caption & String(22 – Len(.Caption), ” “) & .ID
End With
Next i
End With
Debug.Print vbLf
End Sub
And if you like to see a listing of all menubars in Excel, WOrd, Acces and such, have a look at my xlmenufundict available from http://www.jkp-ads.com. You can even see what the Japanese have to put up with for their menus