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.

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.

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 download
and install the program before starting this tutorial.

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.

In Excel, close the Order Form workbook, and then close Excel.

Open the Custom UI Editor

Click the Open button, then select and open the Order Form file.

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.

Click the Insert menu, and click Sample XML

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:

On the Custom UI menu bar, click the Validate button

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

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.

Click OK, to close the message

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.

Close the Custom UI Editor

Open Excel, and open the Order Form file.

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.

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.

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.

On the Excel Ribbon, click the View tab

Click Macros, then click View Macros

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

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.

In Excel, close the Order Form workbook, and then close Excel.

Open the Custom UI Editor

Click the Open button, then select and open the Order Form file.

In the Tab ID line, change the custom tab label from "Contoso"
to "Order Form"

Delete the next two lines, with the groups -- GroupClipboard and
GroupFont. We don't need these in the custom tab.

Also delete the three groups at the end -- GroupEnterDataAlignment,
GroupEnterDataNumber and GroupQuickFormatting.

Next, delete the line for CustomButton03 -- you'll only need 2
buttons, to run the 2 macros.

Finally, change the group label from Contoso Tools to Order Form
Tools.

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.

For button 1, use these settings:

Label: "Clear"

Size: "large"

onAction: "ClearDataEntry"

imageMso:"TableStyleClear"

For button 2, use these settings:

Label: "Print"

Size: "large"

onAction: "PrintOrder"

imageMso:"PrintAreaMenu"

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.

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

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

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

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.

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.

Save your changes, and close the Custom UI Editor.

Open Excel, and open the Order Form file.

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.

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

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.

Close the Visual Basic Editor, and return to Excel.

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.

Create a new blank workbook, and you'll notice that the custom
Order Form tab disappears when the new workbook is active.

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:

In Excel, close the Order Form workbook, and then close Excel.

Open the Custom UI Editor

Click the Open button, then select and open the Order Form file.

Copy one of the existing Button lines, and paste the copied code.

In the copied line, change the ID number to customButton3

Change the label to Home

Change imageMso to image, and type the name of the custom icon
-- ribbonhome

Change the onAction to GoOrder.

The completed XML code will look like this:

Insert Your Custom Icon

To insert your custom icon:

Click the Insert command on the Custom UI Editor's toolbar.

Click the Icons command.

Find and open your custom icon file.

To see the icon, click the + to the left of the CustomUI.xml

Verify the code, then save the file and close the Custom UI Editor

Test the New Button

To test the new button:

Open the Order Form file in Excel.

Select the Products sheet

On the Ribbon's Order Form tab, click the Home button.

Download the Sample File

Download the sample file -- DataValOrderMacro.zip
-- to use with this tutorial. The zipped file is in Excel 2007 / 2010
format, and contains macros.

For the custom icon sample file, click
here to download the workbook. The zipped file is in Excel 2007
/ 2010 format, and contains macros. It also contains the icon image
for the custom tab.