O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:someone@yourcompany.com" & _
      "&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.

New item on the cell context menu sends a range of cells

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required