Excel Ribbon Customization

We can easily customize the Excel ribbon manually and hide certain tabs or add new tabs and buttons. However, the customization does not persist if we share the Excel workbook with other users; it only applies to the local copy of Microsoft Excel. We need to modify the XML code to export the customization with the file. That’s especially helpful when creating Excel add-ins. In this post we see how to customize the ribbon manually and locally, and how to modify the XML code to customize the ribbon programmatically and permanently for a given workbook. Find the list of ribbon image icons in this other page: Microsoft Office ImageMSO Icons

Customize the Ribbon Manually and Locally

We can customize the Excel ribbon of the local copy of Microsoft Excel right-clicking the ribbon or going to File => Options => Customize Ribbon, and then adding or removing commands, tabs, and groups. We can hide existing tabs or remove groups and commands within existing tabs, and we can add/remove new tabs, groups, and commands. That includes existing commands and new commands linked to VBA macros. It is important to mention that we add the commands to groups within tabs. This process cannot really be automated with VBA macros (see more about that later).

In the picture below we have added a new tab called “Macros” with a group name “My Macros” and two commands or buttons to run two macros. But that’s only visible in the local Excel application. We can associate the buttons with a number of graphical icons; the ones below are shown by default.

 

Manipulate the Ribbon with VBA

We can automate almost everything in Excel with VBA. However, there is little we can do when it comes to the ribbon. We can show/hide the entire ribbon as indicated below (we can also show/hide only the tabs with "MinimizeRibbon")

  CommandBars.ExecuteMso "HideRibbon"

It is also possible to add new groups and buttons targeting the CommandBar object. However, it can only be added to the existing Add-ins tab (this tab is usually hidden by default). The code below shows how to add a new button to the default group within the Add-ins tab in the ribbon. The button is added temporarily and only to the local copy of Excel.

 
  Sub AddButtonToRibbon()
      Dim cmdBar As CommandBar
      Dim cmdButton As CommandBarButton
 
      Set cmdBar = Application.CommandBars(1)
      Set cmdButton = cmdBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
 
      With cmdButton
          .Style = msoButtonIconAndCaption
          .Caption = "My Button"
          .FaceId = 59
          .OnAction = "MyMacro"
      End With
  End Sub
 
  Sub MyMacro()
      MsgBox "Welcome to Excel Macro Mania!"
  End Sub
 

The button ca be associated with a graphical icon using the FaceId property (FaceId 59 shows a smiley face). Find all available FaceId icons in this page: Command Bar Buton FaceId

Customize the Ribbon with XML

We need to modify the underlying XML code of an Excel file to permanently customize the ribbon. Once done, the Excel workbook will keep the customization when shared with other users. However, that applies to the target workbook only. We can create an Excel add-in to always have the customization when added to Excel.

We can modify the XML code manually or use a free tool that helps with the process. To do it manually, we need to perform the following tasks:

1. Rename the file extension from Excel extension (most likely .xlsm or .xlam for Excel add-ins) to .zip or .rar and open it with winzip/winrar.

2. Create another folder called customUI within the unzipped Excel structure.

3. Create an XML file in Notepad or other text/code editor, and copy the code below and save it in that folder. The name of the file must be customUI14.xml. The structure of the file responds to the ribbon components to be implemented.

 
  <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="CustomTab" label="Macros">
        <group id="CustomGroup1" label="My Group">
          <button id="btn1" label="large btn" imageMso="HappyFace" size="large" onAction="MyMacro1" />         
          <button id="btn2" label="normal btn1" size="normal" onAction="MyMacro2" />
          <button id="btn3" label="normal btn2" size="normal" onAction="MyMacro3" />
         </group>
       </tab>
    </tabs>
  </ribbon>
  </customUI>
 

Each <tab>, <group>, and <button> element needs to have a unique id and should have a label. They represent the respective components in the ribbon. Additionally, each button is associated with a macro (via onAction), and can have additional properties such as the size (normal or large), an icon associated with the button, and a pop up message or supertip. See the full list of available imageMSO icons in this other page: Microsoft Office ImageMSO Icons

4. In the folder _rels, open the .rels file with Notepad or other text/code editor and add the following relationship (highlighted in bold below) to the XML code.

 
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="R337f5ed26e5b451d" Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="customUI/customUI14.xml"/>
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
  </Relationships>
 

5. Close the files and compress the Excel structure or change the extension back to .xlsm (or .xlam for Excel add-ins).

If everything is done correctly, we now see the new tab, group, and buttons in the Excel ribbon when opening the workbook.

The process can be overwhelming at first. Furthermore, any slight mistake with the names or XML code will prompt an error when opening the file (or won’t show any of the new ribbon components at all). There is an easier way to update all those files and add the XML code using a simple free tool available here: Custom UI Editor for Microsoft Office

1. Open the UI editor and then load the target Excel workbook under File -> Open

2. Modify the XML code as required (follow the instructions above)

3. Save and close

IMPORTANT: The Excel workbook which ribbon you want to customize needs to be closed when using the custom UI editor.

Add Macros for XML Custom Buttons

When modifying the XML code to add custom buttons to the ribbon and linking a macro to the button, we need to pass an argument representing the ribbon control to the macro that runs when clicking a button. See below how the procedure structure looks like.

 
  Sub MyMacro1(control As IRibbonControl)
      MsgBox "You pressed the large button with the smiley face"
  End Sub

Thus, the custom Excel ribbon becomes a user interface to perform any tasks or actions determined by the VBA code in the corresponding macros.

 

Download Excel Ribbon Customization Example