Custom right click contextual menus

[Asator]

I know how to add entries for macros and whatnot in the right click context menu, but how do I add submenus, and how do I assign items to the submenu?

 

[Andrew Poulsom]

The same way that you would with a normal menu:

http://msdn.microsoft.com/en-us/lib...stomExcelContextMenus_AddControlsUsingVBACode

 

[Asator]

Hmm.
I keep getting a new entry for the "Number tools" menu every time I open excel. How do I get rid of these? And how do I get the four items to go into that menu? Also.. the .faceid's don't seem to be showing either.

Any ideas?

personal.xls file:

Private Sub Workbook_Open()
Dim cButFilt1 As CommandBarButton, cButClrFilt As CommandBarButton, cBut_strToNum As CommandBarButton, _
    cBut_strToDate As CommandBarButton, cMenu_NumberSubMenu As CommandBarControl, cButGrtFilt As CommandBarButton, _
    cButLessFilt As CommandBarButton
    
Call DeleteFromCellMenu
Dim ContextMenu As CommandBar
Set ContextMenu = Application.CommandBars("Cell")
On Error Resume Next

Set cButFilt1 = ContextMenu.Controls.Add(temporary:=True)
Set cButClrFilt = ContextMenu.Controls.Add(temporary:=True)

    
With cButFilt1
    .Caption = "Filter By Selection"
    .Style = msoButtonCaption
    .OnAction = "mac_filter_by_selection"
    .FaceId = 458
End With
With cButClrFilt
    .Caption = "Clear Filters"
    .Style = msoButtonCaption
    .OnAction = "mac_clr_filters"
    .FaceId = 478
End With
    
Set cMenu_NumberSubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, temporary:=True)
    With cMenu_NumberSubMenu
        .Caption = "Number tools"
        .Tag = "CustomJN"
        
        Set cButGrtFilt = ContextMenu.Controls.Add(temporary:=True)
        Set cButLessFilt = ContextMenu.Controls.Add(temporary:=True)
        Set cBut_strToNum = ContextMenu.Controls.Add(temporary:=True)
        Set cBut_strToDate = ContextMenu.Controls.Add(temporary:=True)
        With cButGrtFilt
            .Caption = "Filter by greater than or equal to value"
            .Style = msoButtonCaption
            .OnAction = "mac_filter_greater_or_equal"
            .FaceId = 125
        End With
        With cButLessFilt
            .Caption = "Filter by less than or equal to value"
            .Style = msoButtonCaption
            .OnAction = "mac_filter_less_or_equal"
            .FaceId = 125
        End With
        With cBut_strToNum
            .Caption = "Convert column values to numbers"
            .Style = msoButtonCaption
            .OnAction = "mcro_convValtoNums"
            .FaceId = 127
        End With
        With cBut_strToDate
            .Caption = "Convert column values to dates"
            .Style = msoButtonCaption
            .OnAction = "mcro_convValtoDates"
            .FaceId = 125
        End With
    
    End With
On Error GoTo 0
End Sub

 

[Asator]

Edit:
I just realized why the submenus weren't working. Fixed that. Now, how do I get rid of the superfluous "Number tools" entries that are in my right click menu that were there from before I added temporary=true?

Edit 2:
I'm a dork. Forgot the .delete method. Problem solved!

 

[Andrew Poulsom]

See the DeleteFromCellMenu procedure in the link I posted.