Programming Excel with VBA and .NET

Build Context Menus

You can’t change Excel’s context menus through the user interface. Instead, you must use code to add or remove items on a context menu. For example, the following code adds a Send Range item to the context menu displayed when you right-click a selected range of cells:

Sub AddCellMenuItem( )
    Dim cb As CommandBar, cbtn As CommandBarButton
    Dim index As Integer
    ' Get the context menu by name.
    Set cb = Application.CommandBars("Cell")
    ' Add the new menu item.
    Set cbtn = cb.Controls.Add(msoControlButton, , , , True)
    ' Set the caption and action.
    cbtn.Caption = "&Send Range"
    cbtn.OnAction = "SendRange"
End Sub
 
' Procedure used by OnAction property.
Sub SendRange( )
    ' Copy the range.
    Selection.Copy
    ' Display a mail message.
    ThisWorkbook.FollowHyperlink "mailto:[e-mail]" & _
      "&Subject=Selection from " & ActiveSheet.Name
    ' Wait two seconds for message to display.
    Application.Wait Now + TimeSerial(0, 0, 2)
    ' Paste range into message body.
    SendKeys "^v"
End Sub

To see how this works, run AddCellMenuItem to add the new menu item, select a range of cells, right-click, and choose Send Range. Excel creates a new mail message and pastes the range into the message body as shown in Figure 19-11.

Figure 19-11. New item on the cell context menu sends a range of cells

The context menus are CommandBar objects, just like the top-level menu bars, but they have a Type property ...