Programming Excel with VBA and .NET

Create New Context Menus

You can create custom context menus from scratch using the CommandBars collection’s Add method. Once it is created, you control the display of the context menu using the ShowPopup method. The following code demonstrates how to create and display a new context menu similar to the menu in Figure 19-10:

' Module-level variable.
Dim m_cb As CommandBar
 
Sub CreateNewContextMenu( )
    ' Delete the menu bar if it already exists.
    On Error Resume Next
    Application.CommandBars("New").Delete
    On Error GoTo 0
    ' Create a new context menu bar.
    Set m_cb = Application.CommandBars.Add("New", msoBarPopup, False, True)
    ' Add some items to the menu bar.
    With m_cb.Controls.Add(msoControlButton, , , , True)
        .Caption = "Sample &1"
        .OnAction = "Sample1"
    End With
    With m_cb.Controls.Add(msoControlButton, , , , True)
        .Caption = "Sample &2"
        .OnAction = "Sample2"
    End With
    With m_cb.Controls.Add(msoControlButton, , , , True)
        .Caption = "Run &All"
        .OnAction = "TestMenus"
        .BeginGroup = True
    End With
    ' Display the menu.
    m_cb.ShowPopup 100, 100
End Sub

Tip

The CommandBar variable is defined at the module level so you can use it more easily from other parts of your project.