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:
Related
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”