Monday, November 7, 2011

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:

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:


Resources
Other usefull pages:
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:

3 kommentarer:

Ozprey said... at February 1, 2013 at 12:31 AM

Hi, if I add two buttons these are located one next to the other, how do I put them one below the other? Thanks in advance!

Anonymous said... at March 1, 2013 at 6:16 PM

Although I appreciate this article, the code appears to do nothing in Excel 2010 so it would be nice if it could be updated to work with Excel 2010. So far I haven't found how to add a custom ribbon tab with a button using VBA. Maybe I can add a shortcut (right click menu) in Excel 2010 - maybe it works the same as in Excel 2002/2003.

Petros said... at May 21, 2013 at 12:57 AM

To Anonymous:
You can use the Dynamic RibbonX framework with VBA to add custom ribbon tabs and controls without using XML. Please read article below. You can download VBA code in working demos.

http://www.spreadsheet1.com/dynamic-ribbonx.html

Post a Comment