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.
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 ...
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.