Excel VBA: Display the Developer Toolbar or Ribbon

By default the developer toolbar or ribbon (depending on the Excel version) is hidden in Microsoft Excel. In Excel 2007 and later it can be quite tricky to figure out where to enable the developer ribbon if you don't know where to look. To make things even worse, in Excel 2010 they have changed the way of enabling the ribbon.

Excel 2016

To enable the developer tooolbar in Excel 2016 for Office 365, please have a look at Displaying the developer toolbar or ribbon in Excel.

Excel 2010

In Excel 2010 you can display the developer toolbar the following way:

1. Click the green File Button
2. Press Options
3. Make sure that the Customize Ribbon right menu item is selected.
4. In the dropdown list called Customize the Ribbon, select All Tabs.
5. In the group called Main Tabs, make sure that the option Developer is checked.

Excel 2007

To display the developer ribbon, do the following:

1. Click the Office Button
2. Click the Excel Options button at the bottom of the dialog.
3. Ensure that the Popular tab in the left menu is selected (se picture below)
4. Check the option Show Developer tab in the Ribbon

Excel 2003

In older versions of excel the developer ribbon is divided between two toolbars: Control Toolbox and Forms. The toolbars can be displayed the following way:

1. Press View on the main toolbar
2. Select Toolbars
3. Check the toolbar Control Toolbox
4. Check the toolbar Forms

 

Excel VBA: Adding custom Button to the Toolbar or Ribbon

Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer.
Please notice that the following code works best with Excel versions prior to Office 2007. If used in newer versions, from Excel 2007, the button and commandbar will be added to the "Add-Ins" tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. Se below for more information.

' ConstantsPrivate Const COMMANDBAR_NAME As String = "Custom Toolbar"Private Const BUTTON_CAPTION As String = "My Button"' OpenPrivate Sub Workbook_Open()
    ' Variables
    Dim objCommandBar As CommandBar
    Dim objButton As CommandBarButton

    ' Try to get the Commandbar (if it exists)
    On Error Resume Next
    Set objCommandBar = Me.CommandBars(COMMANDBAR_NAME)
    On Error GoTo 0
    
    ' Was the commandbar available?
    If (objCommandBar Is Nothing) Then
        ' Create the commandbar
        On Error Resume Next
        Set objCommandBar = Application.CommandBars.Add(Name:=COMMANDBAR_NAME, Position:=msoBarTop, Temporary:=True)
        On Error GoTo 0
        
        ' Valid commandbar?
        If (Not objCommandBar Is Nothing) Then
            ' Add the buttons to the command bar
            With objCommandBar
                ' Add button
                Set objButton = objCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                
                 ' Set the button properties
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = BUTTON_CAPTION
                    .FaceId = 258
                    .TooltipText = "Do Something"
                    .OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething"
                End With
                
                ' Show the command bar
                .Visible = True
            End With
        End If
    End IfEnd Sub' Before ClosePrivate Sub Workbook_BeforeClose(Cancel As Boolean)
    
    On Error Resume Next
    ' Try to remove the iTrade command bar
    Call Application.CommandBars(COMMANDBAR_NAME).Delete
    
    ' Restore error handling
    On Error GoTo 0
End Sub

The custom event called OnDoSomething must be defined in a global Module. It doesn't work to define the method in the Workbook class. Insert a new Module and add the following code:

Option Explicit

Public Sub OnDoSomething()
    MsgBox "Hello World!"End Sub

You can change the button icon by specifying another FaceId value. To get a list of all available FaceIds on you computer, download and and install the FaceID Browser:

• FaceID Browser

Excel 2007 and later
To dynamically add buttons to the Ribbon you must use a combination of XML and VBA. For more information, please visit:

• How to: Use VBA to Add a Custom Command to the Ribbon 

Resources
Other usefull pages:

• Excel VBA: Adding items to the right-click menu

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:

• FaceID Browser

 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:

• Excel VBA: Add buttons to toolbars/ribbons