VBA TIPS
  Customizing the right click popup menu


Here are examples to add a menu in the right click popup menu. Sub AddMenu() would add a menu named Select a worksheet.

Sub AddSubMenu() would add sub menus with FaceID under the menu named My Custome Macro.

When you'd like to reset menu, run a procedure named Sub ResetMenu()

Note, when you right click on the Row or the Column headers, you cannot see these menus on the popup, because these custom menus has been added to CommandBars("Cell") object. If you'd like to add these menus to Row and Column popup menu, use CommandBars("Row") or CommandBars("Column") instead of CommandBars("Cell") in the code line.

Sub AddMenu Sub AddSubMenu


Example

Place the following in a standard module.

Option Explicit

'This code would add a menu in the right click popup menu
Sub AddMenu()
    Dim cmbPup As CommandBarPopup
    ResetMenu

    Set cmbPup = Application.CommandBars("Cell").Controls.Add _
                 (Type:=msoControlPopup)
    With cmbPup
        .Caption = "Select a worksheet"
        .OnAction = "SelectWks"
        .BeginGroup = True
    End With
End Sub

'This code would add a menu in the right click popup menu and adds sub menus
Sub AddSubMenu()
    Dim cmbPup As CommandBarPopup
    ResetMenu

    Set cmbPup = Application.CommandBars("Cell").Controls.Add _
                 (Type:=msoControlPopup)
    With cmbPup
        .Caption = "My Custome Macro"
        .BeginGroup = True
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Select a worksheet"
            .FaceId = 302
            .OnAction = "SelectWks"
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Select all worksheets"
            .FaceId = 303
            .OnAction = "SelectAllWks"
        End With
    End With
End Sub

'This code would Reset the right click popup menu
Sub ResetMenu()
    Application.CommandBars("Cell").Reset
End Sub

'Sample procedures
Private Sub SelectWks()
    CommandBars("Workbook tabs").ShowPopup
End Sub

Private Sub SelectAllWks()
    Worksheets.Select
End Sub


| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo