Excel – Create and add a custom Ribbon Tab (Menu)


To totally unlock this section you need to Log-in

Adding a custom tab into a specific Office document (2007+ documents) can be very useful if we need to transport, with the document, custom VBA/macro codes. In the following article we will see how to manage this requirement using a free and open source program called Office RibbonX Editor, coded by Fernando Andreu and available at https://github.com/fernandreu/office-ribbonx-editor/releases/tag/v1.6.

You can download OfficeRibbonXEditor-NETFramework.zip and after you unzip it open the OfficeRibbonXEditor.exe to use it (it is a portable and standalone software).

The Office RibbonX Editor gives you an option to insert a customUI.xml file in your Excel workbook that loads when you open the file in Excel 2007and up and/or a customUI14.xml file that only loads when you open the Excel workbook in Excel 2010 and up: all files created in Office 2007+ can be considered as .zip files in which there are few folders and several xml files that defines the content and other document specifications.

Note: Excel versions 2010 and up use the same xml file named customUI14.xml to defines and show custom tabs. Take also note that new custom tabs cannot be added to Office XP/2003 .xls files, because those versions do not have a Ribbon and also because files generated by these legacy versions are binary files and not OpenXML-based files like those in Office 2007+ files.

Important Note: with this approach you will be able to embed new tabs definitions, icons, and VBA code (obviously) directly into a single Office file (Excel, for example).

Manual Method

To create new buttons and/or custom tabs into an Excel file is not needed any external editor (we can use the Office RibbonX Editor just to avoid the "manual" part) and can be done with the following procedure.

Create a folder called customUI and inside this folder an customUI14.xml file (customUI14.txt file renamed to customUI14.xml as extension) with the following code (for a single Custom Tab with a single button), but obviously modify the properties with the name of your function/macro and customizing the IDs with anything you want:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon>
		<tabs>
			<tab id="customTab" label="HeelpBook" insertAfterMso="TabView">
				<group id="customGroup" label="HeelpBook Tools">
					   <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Now open an Excel file (usually .xlsm if it have to include macro/VBA code) using an archiving application (WinZip, WinRAR, 7zip) as it would be a simple compressed file.

Once opened, just drag the new folder customUI into the Office file, like in the following image:

Excel - Create and add a custom Ribbon Tab (Menu)

Once done, open the _rels folder and edit directly that .rels.xml file with Notepad (for example) adding the following text string before the </Relationships> tag (closing tag). You can customize the Id="Rde10b197e67141c2" as you want.

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

Now just save the .rels.xml file and close the Excel file open in WinZip/7zip/WinRAR. After that, just open the Excel file normally and you should see the new tabs and buttons as expected.

Note: if you want multiple tabs and multiple buttons just read the Multiple Tabs and Multiple Groups of Buttons section below into this article.

Office RibbonX Editor

The following shown is the main menu of the program:

Excel - Create and add a custom Ribbon Tab (Menu)

  • Open: Open the Excel file that you want to edit (Be sure it is not open in Excel).
  • Save: Save the RibbonX in the file that you have open in the UI editor (not possible to save when the file is open in Excel).
  • Insert Icons: Insert your own icons in the file (icons will be saved and loaded directly from the file).
  • Validate: Validate your RibbonX so you are sure that there are no typos.
  • Generate Callbacks: This will create macros(callbacks) for each onAction you have in the RibbonX. Select all callbacks and copy them in a normal module in your workbook.
  • Reload on Save: If you have the file open in Excel, to ensure you do not delete changes made in the externally open Excel file, use the button to reload the documents right before they are to be saved.

Opening an Excel file in the Office RibbonX Editor will let you, by right clicking on the file name, to choose:

  • Office 2007 Custom UI Part
  • Office 2010+ Custom UI Part

Or you can use the Insert menu to choose one of the two options.

Excel - Create and add a custom Ribbon Tab (Menu)

The program, once chosen the Office 2010+ Custom UI Part option, will create the customUI14.xml file, and the customUI.xml file if you choose Office 2007 Custom UI Part. After you choose one option or both, you can enter or paste your RibbonX in the correct part.

Example for 2007 and Up

In the following example we will add RibbonX to the file that create a button on the Home tab when you open your file in Excel 2007 and up. We will have to add the RibbonX to the customUI.xml file ("Office 2007 Custom UI Part") .

  • Open a new workbook and save it as Book1.xlsm (Excel Macro-Enabled workbook)
  • Close the workbook
  • Open Book1.xlsm in the Office RibbonX Editor
  • Right click on the File name in the Office RibbonX Editor or use the Insert menu
  • Choose "Office 2007 Custom UI Part" to create the customUI.xml file
  • Paste the RibbonX code below in the right window

Note: If there is no customUI14.xml file it will load the customUI.xml also in Excel 2010 and up.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">
          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Let's note here that we added, into the Home tab (idMso="TabHome") a new group, labeled My Group in which it will be included a single button, labeled Click Me.

If your Ribbon buttons will run macros, you'll need to add a Ribbon callback in each macro's arguments. To see how these are set up, you can use another command in the Office RibbonX Editor.

On the Office RibbonX Editor's menu, click the Generate Callbacks button.

Excel - Create and add a custom Ribbon Tab (Menu)

A new code sheet appears, with all the macros listed as onAction items in your Custom UI code:

Excel - Create and add a custom Ribbon Tab (Menu)

So, now we will have to save the changes in the Office RibbonX Editor (click on the Save button), then close the editor and open the file in Excel, in which we will open the VBA Editor with Alt+F11, we will click on Insert Module and finally we will copy the below macro, for example, in the Module.

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

Note the brackets after each macro name, you can see the ribbon callback: (control As IRibbonControl).

Click on the Save button in the VBA editor, then use Alt+Q to close the VBA editor and finally test the workbook in 2007 and/or in 2010 and up.

Excel - Create and add a custom Ribbon Tab (Menu)

Example for 2010 and Up

The procedure for the "Office 2007 Custom UI Part" is very similar to the 2007 version, but with a difference in the code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">
          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

insertAfterMso (Adding a Custom Tab)

To add a custom tab, not including just a button or multiple buttons inside a group in a pre-existing tab, we will need to change two properties into the XML definition file (customUI14.xml): the insertAfterMso and changing the idMso to id.

For example, to define a new tab with an included button we will need to define a customUI14.xml like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon>
		<tabs>
			<tab id="customTab" label="HeelpBook" insertAfterMso="TabView">
				<group id="customGroup" label="HeelpBook Tools">
					   <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Excel - Create and add a custom Ribbon Tab (Menu)

Note that we will not use, in this case, the idMso property, but only the id, with his label and obviously the insertAfterMso, that will define the position into the ribbon in the Office program.

Note: not only insertAfterMso exist, but also insertBeforeMso exist.

In the example above we will have the new custom tab displayed after the View tab.

The complete Ids that can be used with the insertAfterMso property are the following:

TabHome
TabAddIns
TabDeveloper
TabPageLayoutExcel
TabReview
TabData
TabInsert
TabFormulas

Multiple Tabs and Multiple Groups of Buttons

The following code example is about creating, using customUI14.xml, multiple tabs and multiple groups of buttons (macros/VBA) in an Excel file:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon>
		<tabs>
			<tab id="customTab1" label="Heelp" insertAfterMso="TabView">
				<group id="customGroup1" label="Heelp">
					          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
					          <button id="customButton2" label="Click Me" size="large" onAction="Macro2" imageMso="AboveText" />
					          <button id="customButton3" label="Click Me" size="large" onAction="Macro3" imageMso="MultiplePages" />
				</group>
			</tab>
			<tab id="customTab2" label="Book" insertAfterMso="TabView">
				<group id="customGroup2" label="Book">
					          <button id="customButton4" label="Click Me" size="large" onAction="Macro4" imageMso="GridSettings" />
					          <button id="customButton5" label="Click Me" size="large" onAction="Macro5" imageMso="Lock" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Take note that each tab, group or button element needs to have a different and unique id associated. Obviously you will have also, if not intended otherwise, to associate a different macro/VBA code to each button element.

Excel - Create and add a custom Ribbon Tab (Menu)

imageMso (Office Icons)

There are a lot of icons embedded into Office programs. Using the following link you will be able to see almost all the available icons, and their Ids, in the first column, that can be used into imageMso property, to be associated to buttons.

imageMso Codes

Add Button to the Backstage View

The Backstage View, in Office, is the area program window where you can manipulate the properties of file itself. Below example in Microsoft Excel:

Excel - Create and add a custom Ribbon Tab (Menu)

Excel Backstage view has a three-column structure as shown in above image. The left column has green color with many menu items. If you click a menu item in left Column, options related with the clicked menu will be displayed in other two columns (the one under Info text and the other one under Properties).

Now, to add a button to the Backstage View, on the green column (the first one from left, on which there are Info, New, Open, Save, Save As, etc.) we can use both Office 2007 Custom UI Part and Office 2010 Custom UI Part, as follows:

Office 2007 Custom UI Part

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <officeMenu>
            <button id="MyCustomButton1" label="My Macro" imageMso="HappyFace" onAction="Macro1"/>
        </officeMenu>
    </ribbon>
</customUI>

Office 2010 Custom UI Part

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <backstage>
            <button id="MyCustomButton1" label="My Macro" imageMso="HappyFace" onAction="Macro1" />
    </backstage>
</customUI>

Once save the changes into Office RibbonX Editor, opening the Excel file we can see the new button in the Backstage View. The linking approach of a macro/VBA code to that button is the same of custom tabs.

Excel - Create and add a custom Ribbon Tab (Menu)

Important note: If you have also other code that change the Ribbon you will have to add the xml code for Backstage above the last line </customUI>, so below the code that change the Ribbon. It will not work if you add the backstage xml above the xml code that change the Ribbon.

Office RibbonX Editor

The following are the download links (mirrors) for the Office RibbonX Editor, available for download. The download include the portable versions of this tool in both NET Framework and NET Core versions.

Office RibbonX Editor (.NET Core) Office RibbonX Editor (.NET Framework)