Home > Macros > Basics > Ribbon How to Add Custom Tab to Ribbon Add a custom tab to the Excel Ribbon, using the Custom UI Editor, a free tool. The tab appears when a specific workbook opens, and disappears when the workbook is not active. Requires Excel 2007 or later version. Author: Debra Dalgleish

Thanks to Ron de Bruin, Excel MVP, for the helpful Ribbon customization information on his website, and thanks to Stephen Davanzo, whose sample workbook inspired me to start experimenting with the Ribbon.

Order Form Workbook

In this tutorial, you'll see how to add a custom tab to the Excel Ribbon, that will be visible when the Order Form workbook is active. There will be buttons on the custom tab, to run the two macros that are in the workbook.

Video: Add Custom Ribbon Tab to Workbook

To see the steps for adding a custom Ribbon tab in a workbook, please watch this video tutorial. The written instructions are below the video.

Add Your Own Icon to a Custom Tab

To see the steps for adding your own icon to a custom Ribbon tab in a workbook, please watch this video tutorial. The written instructions are in the Add a Custom Icon section.

Files and Tools Required

Custom UI Editor

We'll use the free tool -- Custom UI Editor to view and edit the Ribbon code. You should get and install the program before starting this tutorial.

You can get the Custom UI Editor from Ron de Bruin's website, or use a newer free tool, Office RibbonX Editor, instead.

Sample Workbook

This example is based on an Order Form workbook, which contains two macros. You can download the sample file, and use it to follow along with this tutorial. The file is zipped, and requires Excel 2007 or later version -- DataValOrderMacro.zip

If you would prefer to create a custom tab your own workbook, write or record at least one macro in the fileS, and make sure that the file is saved as macro-enabled.

Add the Custom UI Part

The first step is to add the Custom UI part in the Custom UI Editor. The Ribbon code will be stored here.

1. In Excel, close the Order Form workbook, and then close Excel.
2. Open the Custom UI Editor
3. Click the Open button, then select and open the Order Form file.
4. Click the Insert menu, then click one of the Custom UI parts.
• If the file will only be used in Excel 2010, or later versions, select that Custom UI Part
• If the file will also be used in Excel 2007, select the Office 2007 Custom UI Part

The Custom UI part will appear below the file name.

Create Sample Ribbon Code

To get started, you can let the Custom UI tool create sample code for you.

1. Click the Insert menu, and click Sample XML
2. Click on Excel - A Custom Tab.

This adds sample code in the code window.

Reading the Custom UI Code

In the xml code, you can see that there is a heading for each part of the custom UI:

• namespace
• ribbon
• tabs
• tab
• group
• button

Each item has a unique ID, a label, and perhaps other properties, such as an icon and a macro that runs when the button is clicked.

For example, here is the code for one of the buttons in the Contoso Tools group. Line breaks were added, to make the code easier to read.

• The ID -- customButton1 -- is unique. Each ID can only used once in the code.
• The label -- ConBold -- is the text that will appear on the button in the Ribbon tab.
• The image -- Bold -- will also appear on the button
• The size is set to Large, so it will be easy to see on the Ribbon.
• The onAction -- conBoldSub -- is the name of the macro that will run when the button is clicked.

Check the Ribbon Code

Before you save the code, the Custom UI Editor can check the code, to make sure that it is valid. For example:

1. On the Custom UI menu bar, click the Validate button

   

2. An error message appears, warning that the date in the namespace line must be changed.

   

3. Click OK, change the date to 2006/01, and click the Validate button again. A different message appears, announcing that the code is well-formed.

   

4. Click OK, to close the message
5. Click the Save button, to save your changes.

Test the Sample Code

To see the custom tab that the sample code creates, you'll go back to Excel.

1. Close the Custom UI Editor
2. Open Excel, and open the Order Form file.
3. The new custom tab appears on the Ribbon, after the Home tab. You can see the ConBold button, in the Contoso Tools group, and its Bold image.

   

4. However, the macros that are used in the sample code are not stored in this workbook, so you'll see a warning message if you click the ConBold button.

   

5. Click OK, to close the message.

Get the Macro Names

In a few minutes, you'll adjust the Ribbon code, so it refers to the macros in the sample workbook. Follow these steps, to see the names.

1. On the Excel Ribbon, click the View tab
2. Click Macros, then click View Macros
3. In the list, you can see the names of the two macros in this workbook -- ClearDataEntry and PrintOrder.
   • The ClearDataEntry macro clears the contents of cells B6:D10 on the Order Form sheet
   • The PrintOrder macro shows the Order Form sheet in Print Preview, so you can check it before printing



4. Click Cancel, to close the Macro window.

Change the Ribbon Code

Now that you've seen the sample custom tab, you can adjust the code, so it runs the macros in the Order Form workbook.

1. In Excel, close the Order Form workbook, and then close Excel.
2. Open the Custom UI Editor
3. Click the Open button, then select and open the Order Form file.
4. In the Tab ID line, change the custom tab label from "Contoso" to "Order Form"
5. Delete the next two lines, with the groups -- GroupClipboard and GroupFont. We don't need these in the custom tab.

   

6. Also delete the three groups at the end -- GroupEnterDataAlignment, GroupEnterDataNumber and GroupQuickFormatting.
7. Next, delete the line for CustomButton03 -- you'll only need 2 buttons, to run the 2 macros.
8. Finally, change the group label from Contoso Tools to Order Form Tools.
9. To test your changes, click the Validate button. You should see the message announcing that the code is well-formed. If not, check the code for obvious errors, or close without saving, and then try again.

Change the Button Code

Next, you'll change the button code, to run the macros in the Order Form workbook.

1. For button 1, use these settings:
   • Label: "Clear"
   • Size: "large"
   • onAction: "ClearDataEntry"
   • imageMso:"TableStyleClear"
2. For button 2, use these settings:
• Label: "Print"
• Size: "large"
• onAction: "PrintOrder"
• imageMso:"PrintAreaMenu"
3. Then, validate the code, and save the changes.

Note: To see the available icons that you can use as the image Mso, download the Icon Gallery available from the Microsoft website: Office 2007 Icon Gallery. That page has instructions for downloading and using the Gallery.

Set Up the Macros

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 Custom UI Editor.

1. On the Custom UI Editor's menu, click the Generate Callbacks button.

   

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

   

3. In the brackets after each macro name, you can see the ribbon callback: (control As IRibbonControl)
4. You could copy this code, and paste it into the Visual Basic Editor in Excel, and fill in the code for each callback. Or, modify each of your existing macros, to add that ribbon callback.
5. To return to the Custom UI code window, click the workbook name at the left of the Custom UI Editor.

Open the Order Form File

After you have made the Custom UI changes, you can close the Custom UI Editor, and test the file again, in Excel.

1. Save your changes, and close the Custom UI Editor.
2. Open Excel, and open the Order Form file.
3. To see the VBA code, press Alt + F11, and view the code in the modNavigate module. You'll see that each macro needs to have the Ribbon Callback added.

   

4. In the brackets after each macro's name, paste the callback code -- control As IRibbonControl

   

5. Click the Save button, to save the changes

Test the Ribbon Buttons

Now that everything is set up, you can test the buttons on the Order Form custom tab.

1. Close the Visual Basic Editor, and return to Excel.
2. To test the custom tab items, click on the Order Form tab, and click one of the buttons. For example, click the Clear button, to see entries cleared from cells B6:D10.

   

3. Create a new blank workbook, and you'll notice that the custom Order Form tab disappears when the new workbook is active.
4. Switch to the Order Form workbook, and the custom tab will reappear.
   

Add a Custom Icon

In addition to using the built-in icons, you can create your own icons for the custom tab.

For this example:

• a simple icon was created in MS Paint, using the Arrow drawing tool, and rotated 90°, then filled with blue.
• The icon is square (100x100) and saved as ribbonhome.png.

  

Another macro, GoOrder, was added to the sample file, to activate the Order Form sheet, and the new button will run that macro.

Add the New Button

To add a new button with the custom icon:

1. In Excel, close the Order Form workbook, and then close Excel.
2. Open the Custom UI Editor
3. Click the Open button, then select and open the Order Form file.
4. Copy one of the existing Button lines, and paste the copied code.
5. In the copied line, change the ID number to customButton3
6. Change the label to Home
7. Change imageMso to image, and type the name of the custom icon -- ribbonhome
8. Change the onAction to GoOrder.

The completed XML code will look like this:

Insert Your Custom Icon

To insert your custom icon:

1. Click the Insert command on the Custom UI Editor's toolbar.
2. Click the Icons command.
3. Find and open your custom icon file.
4. To see the icon, click the + to the left of the CustomUI.xml
5. Verify the code, then save the file and close the Custom UI Editor

Test the New Button

To test the new button:

1. Open the Order Form file in Excel.
2. Select the Products sheet
3. On the Ribbon's Order Form tab, click the Home button.

How to Remove Custom Ribbon Tab

If you no longer need the custom Ribbon tab that you added to a workbook, follow these steps to remove the custom tab.

• In Excel, close the Order Form workbook, and then close Excel.
• Open the Custom UI Editor, or the RibbonX Editor
• Click the Open button, then select and open the Order Form file.
• In the list at the left, right-click on the customUI.xml object
• In the pop-up menu, click the Remove command

• A confirmation messages appears, with the title, Remove XML Part, and the following message:
  • This action cannot be undone. Are you sure you want to continue?
• If you want to remove the customUI.xml, click Yes
  • To cancel, click No

• Next, in the RibbonX Editor toolbar, click the Save button
• Close the RibbonX Editor, and then re-open your file in Excel

Get the Sample File

1. Basic: Get the basic Custom Ribbon Tab sample file -- to follow along with the steps in this tutorial, and add the XML code. The zipped file is in Excel 2007 / 2010 format, and contains macros.
2. Basic Completed: Get the completed version of the Custom Ribbon Tab sample file for this tutorial. The zipped file is in Excel 2007 / 2010 format, and contains macros. It has both the Office 2007 and Office 2010 Custom UI Parts.
3. Custom Icon: For the custom icon sample file, click here to get the workbook. The zipped file is in Excel 2007 / 2010 format, and contains macros. It also contains the icon image for the custom tab.

Related Tutorials

Excel Ribbon -- Getting Started

Ribbon Tab - Your Macros

Customize Quick Access Toolbar (QAT)

Create a UserForm With ComboBoxes

VBA Code, Copy to a workbook