Monday, November 7, 2011

Excel VBA: Adding custom buttons to the Right-Click popup menu

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 your self 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:


 










Resources
For more information on how to add buttons to CommandBars, please check out:
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

1 kommentarer:

Anonymous said... at February 16, 2013 at 5:55 AM

The sub is worngly named in:
.OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething"
It should be:
.OnAction = "'" & ThisWorkbook.Name & "'!DoSomething"

Post a Comment