In Microsoft Excel it is possible to extend the right click popup menu by using VBA. The right click menu is implemented as a special CommandBar (like the toolbars in Excel 2003) with the name “Cell”.
Customs buttons are added before the popup menu is displayed. It is up to yourself to make sure that any custom buttons are removed when they are no longer in use. Otherwise you would end up adding a new button every time the right menu is about to be displayed.
Option Explicit ' Constants Private Const BUTTON_CAPTION As String = "My Button" ' Before Right Click Private Sub Workbook_SheetBeforeRightClick(ByVal objSheet As Object, ByVal Target As Range, Cancel As Boolean) Dim objButton As CommandBarButton ' Remove the e-mail popup menu On Error Resume Next With Application.CommandBars("Cell") Call .Controls(BUTTON_CAPTION).Delete End With On Error GoTo 0 ' Is the sheet1? If (objSheet Is Sheet1) Then ' Add the special button Set objButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True, Before:=1) ' Set the button properties With objButton .Style = msoButtonIconAndCaption .Caption = BUTTON_CAPTION .FaceId = 258 .TooltipText = "Do Something" .OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething" End With End If End Sub
Notice that the method SheetBeforeRightClick is overriden for the Workbook (ThisWorkbook) object, and not the worksheet, even though it is displayed on the worksheet. We are doing a check in the VBA code before the right click menu is displayed to ensure that it is only displayed for the worksheet named “Sheet1”.
Please notice that all actions, i.e. OnAction events, must be stored in a public module. It is not possible to define the OnDoSomething method in the workbook or the worksheet classes. Add a new Module (Module1) and add the following code:
Option Explicit Public Sub DoSomething() MsgBox "Hello World!" End Sub
To specify an icon for the new button, a numeric value must be set for the FaceId property of the button. There is no built-in way of displaying all possible FaceIds. However, you can download and install the Excel add-in called Face ID Browser to display all possible button icons:
The final result will display the following custom menu item on the right click menu:
For more information on how to add buttons to CommandBars, please check out:
One thought on “Excel VBA: Adding custom buttons to the Right-Click popup menu”