Custom Excel Ribbon

The Excel Ribbon can be customized to provide a user-friendly way to use commands and VBA macros. There are different ways to customize the ribbon that provide different levels of customization and functionality.

XML Components

Note: This technique may no longer work.

The Excel ribbon can be customized using Office Open XML formats files. An Excel file is essentially a zip file containing the underlying components of the Excel file. Changing the file extension to .zip allows access to the underlying files of the Excel file and makes it possible to add XML components representing ribbon tabs, groups, and controls.

Process to Customize UI

1. Backup File

First make a backup of the original file you are going to customize. Files could be corrupted if the process is not done correctly.

2. Show File Extensions

In the Windows File Explorer check File Name Extensions to show file name extensions.

3. Change File Extension

Change the file extension of the file to zip. Right-click the file and select rename to edit the file extension. You will be prompted with a warning. Click yes.

4. Add Custom UI Folder and File

Create a folder on the Desktop called customUI and inside the folder create a file called customUI.xml. Add XML to the file, save, and copy the folder into the zip folder.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon> 
    <tabs> 
      <tab id="MyCustomTab" label="Custom Tab"> 
        <group id="MyGroup" label="Custom Group">
          <button id="MyButton" imageMSO="HappyFace" label="Hello World" size="large" onAction="HelloWorld"/> 
        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

The zip folder should like something like the image below:

5. Add a Relationship to rels file

Open the _rels folder and copy the .rels file to the Desktop. Inside the .rels file add a Relationship tag for the custom UI as the last tag inside the Relationships tag. Copy the .rels file back to the zip file and overwrite it.

<Relationship Id="MyCustomUI" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" />

6. Change Extension Back

Change the file extension back. If you recieve an error message saying the file was corrupted check that your XML is correct and the steps were followed correctly. If everything was done correctly you should be able to see your custom tab on the ribbon.

7. Add VBA to Handle Custom UI Controls

Add the procedures specified by callbacks and onAction attributes. To find the correct procedure signatures to use for a given type of control see here.

Option Explicit

Public Sub HelloWorld(ByVal Button As IRibbonControl)

    MsgBox "Hello, World!"

End Sub

Custom UI XML and VBA

To create the custom UI, XML elements must be added to the customUI.xml file. The root element is the customUI element. Inside the customUI element, there is a ribbon element which contains tabs which contain groups which contain controls. Elements can have certain attributes such as id, label, onAction, etc. Certain types of elements can be given specific attributes that use callback functions. For information about specific elements see the Elements section of the Microsoft Custom UI XML Specification. For more information about using these Elements, their attributes, and how to write VBA code to handle callbacks see this 3-part article: Part 1, Part 2, Part 3.

button

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon> 
    <tabs> 
      <tab id="MyCustomTab" label="Custom Tab"> 
        <group id="MyGroup" label="Custom Group">
          <button id="MyButton" imageMso="HappyFace" label="Hello World" size="large" onAction="HelloWorld"/> 
        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

Option Explicit

Public Sub HelloWorld(ByVal Button As IRibbonControl)

    MsgBox "Hello, World!"

End Sub

checkBox

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
   <ribbon> 
     <tabs> 
       <tab id="MyCustomTab" label="Custom Tab"> 
         <group id="MyGroup" label="Custom Group"> 
           <button id="MyButton" imageMso="HappyFace" label="Hello World" size="normal" onAction="HelloWorld" />
           <checkBox id="checkBox" label="Check This" getPressed="CaptureState" onAction="CaptureState"/>
         </group > 
       </tab> 
     </tabs> 
   </ribbon> 
</customUI>

Option Explicit

Private CheckBoxValue As Boolean

Public Sub HelloWorld(ByVal Button As IRibbonControl)

    MsgBox "Checkbox Value is: " & CheckBoxValue

End Sub

Public Sub CaptureState(CheckBox As IRibbonControl, ByRef ReturnValue)

    CheckBoxValue = ReturnValue

End Sub

comboBox

Comboboxes can be given static items by adding them directly into the XML or they can be filled dynamically using the combobox's callbacks.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
   <ribbon> 
     <tabs> 
       <tab id="MyCustomTab" label="Custom Tab"> 
         <group id="MyGroup" label="Custom Group"> 
           <button id="MyButton" imageMso="HappyFace" label="Hello World" size="normal" onAction="HelloWorld" />
           <comboBox id="MyCombobox" label="Combo Box" getText="GetText" onChange="GetText">
             <item id="Item1" label="1" />
             <item id="Item2" label="2" />
             <item id="Item3" label="3" />
           </comboBox>
         </group > 
       </tab> 
     </tabs> 
   </ribbon> 
</customUI>

Option Explicit

Private ComboBoxText As String

Public Sub HelloWorld(ByVal Button As IRibbonControl)

    MsgBox "Combobox contains: " & ComboBoxText

End Sub

Public Sub GetText(ComboBox As IRibbonControl, Text)

    ComboBoxText = Text

End Sub

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Init"> 
    <ribbon> 
        <tabs> 
        <tab id="MyCustomTab" label="Custom Tab"> 
            <group id="MyGroup" label="Custom Group"> 
            <button id="MyButton" imageMso="HappyFace" label="Hello World" size="normal" onAction="HelloWorld" />
            <comboBox id="MyCombobox" label="Combo Box" getText="GetText" getItemCount="GetItemCount" getItemID="GetItemID" getItemLabel="GetItemLabel" onChange="GetText"></comboBox>
            </group > 
        </tab> 
        </tabs> 
    </ribbon> 
</customUI>

Option Explicit

Private pRibbon         As IRibbonUI
Private ComboBoxItems() As String
Private ComboBoxText    As String

Public Sub Init(Ribbon As IRibbonUI)

    Set pRibbon = Ribbon

    ReDim ComboBoxItems(0 To 2)

    ComboBoxItems(0) = "One"
    ComboBoxItems(1) = "Two"
    ComboBoxItems(2) = "Three"

End Sub

Public Sub HelloWorld(ByVal Button As IRibbonControl)

    MsgBox "Combobox contains: " & ComboBoxText

End Sub

Public Sub GetText(ComboBox As IRibbonControl, Text)

    ComboBoxText = Text

End Sub

Public Sub GetItemCount(ComboBox As IRibbonControl, Count)

    Count = UBound(ComboBoxItems) - LBound(ComboBoxItems) + 1

End Sub

Public Sub GetItemID(ComboBox As IRibbonControl, Index As Integer, ID)

    ID = "ComboBoxItem" & Index + 1

End Sub

Public Sub GetItemLabel(ComboBox As IRibbonControl, Index As Integer, Label)

    Dim Arr(0 To 2) As String
    Arr(0) = "One"
    Arr(1) = "Two"
    Arr(2) = "Three"

    Label = ComboBoxItems(Index)

End Sub

dropDown

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"> 
    <ribbon> 
        <tabs> 
            <tab id="MyCustomTab" label="Custom Tab"> 
                <group id="MyGroup" label="Custom Group">  
                    <dropDown id="dropDown" label="DropDown" getItemCount="GetItemCount" getItemID="GetItemID" getItemLabel="GetItemLabel" onAction="OnAction">
                    <button id="MyButton" label="Button" onAction="ButtonOnAction" />
                    </dropDown>
                </group > 
            </tab> 
        </tabs> 
    </ribbon> 
 </customUI>

Option Explicit

Private pRibbon          As IRibbonUI
Private pDropDownItems() As String
Private pSelectedID      As String
Private pSelectedIndex   As String

Public Sub OnLoad(Ribbon As IRibbonUI)

    Set pRibbon = Ribbon

    ReDim pDropDownItems(0 To 2)

    pDropDownItems(0) = "One"
    pDropDownItems(1) = "Two"
    pDropDownItems(2) = "Three"

End Sub

Public Sub GetItemCount(DropDown As IRibbonControl, Count)

    Count = UBound(pDropDownItems) - LBound(pDropDownItems) + 1

End Sub

Public Sub GetItemID(DropDown As IRibbonControl, Index As Integer, ID)

    ID = "Item" & Index + 1

End Sub

Public Sub GetItemLabel(DropDown As IRibbonControl, Index As Integer, Label)

    Label = pDropDownItems(Index)

End Sub

Public Sub OnAction(DropDown As IRibbonControl, SelectedID As String, SelectedIndex As Integer)

    pSelectedID = SelectedID
    pSelectedIndex = SelectedIndex

End Sub

Public Sub ButtonOnAction(Button As IRibbonControl)

    If pSelectedIndex = vbNullString Then
        MsgBox "No Item Selected"
    Else
        MsgBox "Selected Item: " & pSelectedIndex & ", " & pSelectedID & ", " & pDropDownItems(pSelectedIndex)
    End If

End Sub

editBox

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
    <ribbon> 
        <tabs> 
        <tab id="MyCustomTab" label="Custom Tab"> 
            <group id="MyGroup" label="Custom Group"> 
                <editBox id="MyEditBox" label="Edit Box" getText="CaptureText" onChange="CaptureText" />
                <button id="MyButton" label="Button" imageMso="HappyFace" onAction="ButtonClick" />
            </group > 
        </tab> 
        </tabs> 
    </ribbon> 
</customUI>

Option Explicit

Private EditBoxText As String

Public Sub CaptureText(EditBox As IRibbonControl, Text As String)

    EditBoxText = Text

End Sub

Public Sub ButtonClick(Button As IRibbonControl)

    MsgBox EditBoxText

End Sub

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
    <ribbon> 
        <tabs> 
            <tab id="MyCustomTab" label="Custom Tab"> 
                <group id="MyGroup" label="Custom Group">  
                    <menu id="MyMenu" label="Menu" imageMso="HappyFace" >
                        <button id="MyMenuButton1" label="Menu Button 1" imageMso="Heart" onAction="Heart"/>
                        <button id="MyMenuButton2" label="Menu Button 2" imageMso="Spade" onAction="Spade" />
                        <menuSeparator id="MyMenuSeparator" />
                        <button id="MyMenuButton3" label="Menu Button 3" imageMso="Diamond" onAction="Diamond" />
                        <button id="MyMenuButton4" label="Menu Button 4" imageMso="Club" onAction="Club" />
                    </menu>
                </group > 
            </tab> 
        </tabs> 
    </ribbon> 
 </customUI>

Option Explicit

Public Sub Heart(Button As IRibbonControl)

    MsgBox "Heart"

End Sub

Public Sub Spade(Button As IRibbonControl)

    MsgBox "Spade"

End Sub

Public Sub Diamond(Button As IRibbonControl)

    MsgBox "Diamond"

End Sub

Public Sub Club(Button As IRibbonControl)

    MsgBox "Club"

End Sub

splitButton

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
    <ribbon> 
        <tabs> 
        <tab id="MyCustomTab" label="Custom Tab"> 
            <group id="MyGroup" label="Custom Group">  
                <splitButton id="MySplitButton" size="large" >
                    <button id="MyButton" imageMso="HappyFace" label="Split Button" onAction="MyButtonClick" />
                    <menu id="MyMenu">
                        <button id="MyMenuButton1" label="Menu Button 1" onAction="MyMenuButtonClick1" />
                        <button id="MyMenuButton2" label="Menu Button 2" onAction="MyMenuButtonClick2" />
                    </menu>
                </splitButton>
            </group > 
        </tab> 
        </tabs> 
    </ribbon> 
</customUI>

Option Explicit

Public Sub MyButtonClick(Button As IRibbonControl)

    MsgBox "My Button Clicked"

End Sub

Public Sub MyMenuButtonClick1(Button As IRibbonControl)

    MsgBox "My First Menu Button Clicked"

End Sub

Public Sub MyMenuButtonClick2(Button As IRibbonControl)

    MsgBox "My Second Menu Button Clicked"

End Sub

toggleButton

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
    <ribbon> 
        <tabs> 
        <tab id="MyCustomTab" label="Custom Tab"> 
            <group id="MyGroup" label="Custom Group"> 
                <toggleButton id="MyToggleButton" label="Toggle Button" getPressed="CaptureState" onAction="CaptureState" />
            </group > 
        </tab> 
        </tabs> 
    </ribbon> 
 </customUI>

Option Explicit

Private ToggleState As Boolean

Public Sub CaptureState(ToggleButton As IRibbonControl, State)

    ToggleState = State

    MsgBox ToggleState

End Sub

VSTO

Visual Studio Tools for Office or VSTO are tools that can be installed and used with Visual Studio to develop solutions that work with Microsoft Office. VSTO can be used to customize the Excel ribbon either as an Add-In which works with any Excel Workbook or as a standalone Excel Workbook. VSTO solutions can be created using C# or VB.

Add a new item to a VSTO project and select Ribbon (XML or Visual Designer).

When using the Visual Designer add controls using the Toolbox. Open the toolbox by pressing Ctrl + Alt + X or by selecting it from the View dropdown.

Drag controls from the Toolbox to the ribbon and then add code to the controls.

Office Add-Ins

Office Add-Ins can be used to customize the UI. To customize the ribbon use Add-In Commands. See the Microsoft documentation for:

Manually Edit the Ribbon

Ribbon tabs can be added manually by navigating to File → Options → Customize Ribbon and then clicking the New Tab button. Groups can be added to a tab by clicking New Group. Macros and built-in commands can be added to the custom tab to provide functionality. Commands added to a custom tab can be renamed and assigned a new icon by highlighting the command and clicking rename. Customizations can be exported to customization files and then imported again later. To reset an individual ribbon or all ribbons, select from the Customizations drop down.

Add-Ins Tab

Adding a custom CommandBar will cause the Add-Ins tab to appear on the ribbon. To add a CommandBar use the Application.CommandBars property to retrieve the CommandBars collection and call the CommandBars.Add method. To add controls to the CommandBar, use the CommandBar.Controls property to get the CommandBarControls collection and use the CommandBarControls.Add method.

When using the CommandBarControls.Add method only certain types of controls can be created. Pass a valid member of the MsoControlType enumeration to the Type argument of the Add method to create a specific control.

Type	Description
msoControlButton	Creates a button.
msoControlEdit	Creates a Text Box.
msoControlDropdown	Creates a Dropdown.
msoControlComboBox	Creates a combo box.
msoControlPopup	Creates a button which opens a popup.

Some types of controls have a dedicated class that can be declared and others do not.

Class	Description
CommandBarControl	A generic control class that can be used to represent various types of controls.
CommandBarButton	Represents a button on the CommandBar.
CommandBarComboBox	Represents a combo box on the CommandBar.
CommandBarPopup	Represents a popup control on the CommandBar.

'ThisWorkbook

Option Explicit

Private Const COMMAND_BAR_NAME As String = "CustomCommandBar"

Public CustomCommandBar As CommandBar

Private Sub Workbook_AddinInstall()
    
    Dim CBColl As CommandBars
    Set CBColl = Application.CommandBars
    
    'https://docs.microsoft.com/en-us/office/vba/api/office.commandbars.add
    Set CustomCommandBar = _
    CBColl.Add(Name:=COMMAND_BAR_NAME, Position:=msoBarTop, MenuBar:=False, Temporary:=True)
    CustomCommandBar.Visible = True
    
    'https://docs.microsoft.com/en-us/office/vba/api/office.commandbarcontrols.add
    
    'Button
    With CustomCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
        .Style = msoButtonCaption
        .Caption = "Button"
        .OnAction = "HelloWorld"
    End With
    
    'Combobox
    With CustomCommandBar.Controls.Add(Type:=msoControlComboBox, Temporary:=True)
        .AddItem "A"
        .AddItem "B"
        .AddItem "C"
        .Caption = "Combobox:"
    End With
    
    'Popup
    With CustomCommandBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
        .Caption = "Popup"
        'PopupButton
        With .Controls.Add(Type:=msoControlButton, Temporary:=True)
           .Style = msoButtonCaption
           .Caption = "Popup Button!"
           .OnAction = "HelloWorld"
        End With
    End With
    
    'TextEdit
    With CustomCommandBar.Controls.Add(Type:=msoControlEdit, Temporary:=True)
        .Caption = "TextEdit:"
    End With
    
    'Dropdown
    With CustomCommandBar.Controls.Add(Type:=msoControlDropdown, Temporary:=True)
        .AddItem "A"
        .AddItem "B"
        .AddItem "C"
        .Caption = "Dropdown:"
    End With
    
End Sub

Private Sub Workbook_AddinUninstall()

    'Delete Custom CommandBar
    
    If Not CustomCommandBar Is Nothing Then
        CustomCommandBar.Delete
        Set CustomCommandBar = Nothing
    End If
    
End Sub

'Module1

Option Explicit

Public Sub AlertMessage()

    MsgBox "Hello, World!"

End Sub