Home > Macros > Basics > Ribbon Excel Ribbon - Getting Started See how a custom tab is added to the Excel Ribbon, and set up macros to run when a Ribbon button is clicked. Requires Excel 2007 or later version Author: Debra Dalgleish

Parts Database Example

This example is based on the parts database workbook, in which you enter data in a UserForm with drop down lists. The data is stored on a hidden sheet, and a pivot table summarizes the data in an Inventory report. You can learn how to set up the worksheets and the UserForm on this page: UserForm With ComboBoxes

In this tutorial, you'll see how to add a custom tab to the Excel Ribbon, instead of using worksheet buttons to run the macros.

Here is the Ribbon in Excel 2010, with a custom tab named Db Macros (short for database macros).

Video: Excel Ribbon Code Introduction

Watch this short video to see the steps for viewing and modifying Ribbon code in the Custom UI Editor tool. Then, in Excel, make a minor change to your macros, so they will run when a Ribbon button is clicked.

The written instructions are below the video.

View Ribbon Code with Custom UI Editor

The easiest way to view and edit the Ribbon code is to download and install the Custom UI Editor. This free program lets you see the existing code in a file, or create your own.

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

To see the Ribbon code in the Parts Database file:

1. Download the sample Parts Database With Ribbon file
2. Close the Parts Database file, and close Excel
3. Install and open the Custom UI Editor
4. Click the Open button on the menu bar.

1. Locate and select the Parts Database file, and click Open

In the Custom UI Editor, you can see the workbook and the customUI.xml file at the left. The custom xlm code is shown at the right.

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

For each item that you create, you'll incude 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 the Hide Database button on the Db Macros tab.

• The ID -- DbG02B02 -- is a shortened version of Database tab, group 2, button 2. You could use a text only ID instead, if you prefer, as long as the ID is only used once in the code.
• The label -- Hide Database -- is the text that will appear on the button in the Ribbon tab.
• The image -- Lock -- will also appear on the button
• The size is set to Large, so it will be easy to see on the Ribbon.
• The onAction -- HideDatabase -- is the name of the macro that will run when the button is clicked.

You can also add a screen tip or super tip in the Ribbon.

• Screen tip: In the Parts Database, there is a screen tip for the Show Lists button, which is highlighted in yellow in the screen shot below.
• Super tip: Below the screen tip, circled in blue, is a super tip, which explains how to use the lists.

Check the Ribbon Code

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

1. In the first group, change the second button's ID from DbG01B02 to DbG01B01
2. On the Custom UI menu bar, click the Validate button

   

3. An error message appears, warning that the ID is a duplicate.

   

4. Click OK, change the button ID back to DbG01B02 and click the Validate button again. A different message appears, announcing the the code is well-formed.

   

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

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 Parts Database File

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

1. Save your changes, and close the Custom UI Editor.
2. Open Excel, and open the Parts Database file.
3. If a security warning appears, click the Enable Content button, so the macros will run.

   

4. To see the VBA code, press Alt + F11, and view the code in the modNavigate module. You'll see that each macro has the Ribbon Callback added.

For an explanation of the UserForm that is in the workbook, and its code, please see this page: UserForm With ComboBoxes

Test the Ribbon Buttons

Now that everything is set up, you can test the buttons on the Db Macros Ribbon tab.

1. Close the Visual Basic Editor, and return to Excel.
2. To test the custom tab items, click on the Db Macros tab, and click one of the buttons. For example, click the Show Database button, to see the hidden database sheet.
   Note: This button does not have a screen tip or super tip, so its label shows up as a screen tip -- Show Database.

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

Get the sample file -- Parts Database With Custom Ribbon Tab. The zipped file is in Excel 2007 / 2010 format, and contains macros.

Credits

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.

Related Tutorials

Excel Ribbon -- Add Custom Tab

Ribbon Tab - Your Macros

Create a UserForm With ComboBoxes

VBA Code, Copy to a workbook