The Easy Way to Find Right Click Menus

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
    Next ctl
End Sub

RCFind1 RCFind2 RCFind3
Posted in Uncategorized

2 thoughts on “The Easy Way to Find Right Click Menus

  1. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.