Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook

Do you have a control sheet in your workbook which holds all of your macros and settings which you need to keep navigating to/from in order to update your workbook? Maybe you’ve tried to solve this problem by using combo boxes, but find that you can never get them to look quite right? Do you wish there was a better way?

Well, there is – your workbook can have a custom ribbon.

Wondering why you haven’t heard of this before? Because Microsoft do not publicise it and you will only find very limited documentation. You may find some resources on forums but a lot of it is so old that the links no longer work. There is one book which I would recommend (‘RibbonX Customizing the Office 2007 Ribbon’ by Robert Martin, Ken Puls and Teresa Hennig) but it is out of print so you may not be able to get your hands on a hard copy.

The good news is that I’m going to show you how to create a basic ribbon in this short article. We will then dive into more detail over the coming months so you too can customise your ribbons to look exactly as you want.

Note: I have chosen to use Notepad for the ribbon XML editing as a piece of software that should be universally available, but you can do the same steps in the XML editor of your choice.

4 Steps for creating a basic custom ribbon:

Step 1 – Create the Workbook: Create a new macro-enabled workbook and save it (I have called mine ‘HelloWorld.xlsm’).

Step 2 – Add the VBA code Module: Add the VBA code module which will contain your Ribbon code (mdlRibbon), and paste in the following code (which contains a basic “Hello World” macro (prcRibHelloWorld) that I want my ribbon to run):

Option Explicit

Public ribRibbon As IRibbonUI

Sub prcRibbonOnLoad(ribbon As IRibbonUI)

    Set ribRibbon = ribbon

End Sub

Sub prcRibHelloWorld(control As IRibbonControl)

    MsgBox "Hello World"

End Sub

Then save the file as a Macro-Enabled Workbook. Name it “HelloWorld.xlsm”.

Step 3 – Add the Ribbon:

3a: In the same folder as “HelloWorld.xlsm”, create a new folder named “customUI”. Within this customUI folder, use Notepad to create a “customUI.xml” file.

Add the following XML into customUI.xml to define the appearance of the ribbon:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="prcRibbonOnLoad">

<ribbon>

<tabs>

<tab id="tabMyTab" label="My New Tab" insertAfterMso="TabHome">

<group id="grpHelloWorld" label="Hello World">

<button id="btnHelloWorld" onAction="prcRibHelloWorld" size="large" imageMso="ShapeSmileyFace"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

The image on the next page shows you what the key elements of the XML are doing.

3b: As you may be aware from Tip #441, a “.xlsm” file is really a ZIP compressed archive file. Rename “HelloWorld.xlsm” to “HelloWorld.zip”, then double-click the ZIP file to open it. (You will need to close the file before doing this step.)

3c: Copy the customUI folder you created in 3a into the zip file (do not unzip the file as this may cause errors when you try to turn it back into a .xlsm).

3d: We also need to update the ‘.rels’ references file within the ‘_rels’ folder of the zip file. Drag the .rels file out of the zip to create a copy and use notepad to add the following text before the closing </Relationships> at the end of the file, and save it:

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

Note: In the screenshot above I have added some additional line breaks to make it easier to read.

3e: Drag your new ‘.rels’ file back into the zip file and replace the current version.

3f: Rename HelloWorld.zip back to HelloWorld.xlsm.

Step 4: Open HelloWorld.xlsm and you should see the new ribbon tab (‘My New Tab’) after ‘Home’. Pressing the smiley face will run ‘prcHelloWorld’ and show a “Hello World” message box.

You have now created your first ribbon! There is a lot more that can be done including adding more buttons, dropdown boxes, menus, and text. I hope to take you through some of these over the next few months.