How to Customize the Ribbon in Excel Using XML

If you want to customize the ribbon in Excel then you know simply changing doing it through the front end is only going to work on your computer. The customization isn’t technically saved within the file and it won’t move from one file to another unless you actually adjust the xml.

It’s by no means an easy process, but if you’re just looking to add a custom tab with some buttons for some macros that you have, I can show you a quick way to do that as painlessly as possible. It’s by no means comprehensive, but it’ll get the job done.

Step 1: Open your Excel file in a program like Winzip/7-Zip

If you right-click on your Excel file you should have an option that says Open Archive if you have a program like Winzip or 7-Zip installed. There, you should see something that looks like this:

These are the files and folders that are within the Excel file itself.

Step 2: Create a folder called customUI

Step 3: Open up notepad and paste the following into it:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group id="myMacros" label="My Macros">
<button id="Macro1" label="Macro1" imageMso="HappyFace" size="large" onAction="cbMacro1" />
<button id="Macro2" label="Macro2" imageMso="TableDrawTable" size="large" onAction="cbMacro2" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

What the code will do is add two buttons to the end of the Home tab. I’ll show you how to modify it further down this post.

Save the file as customUI.xml

Step 4: Put the file into the customUI folder that you created in Step 2

Step 5: Back in the archive, navigate to the _rels folder and open the .rels file (in Notepad)

Find this line:
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

and then insert the following right after it:

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

Step 6: Close the archive, save changes and open the file

You should now see the two buttons at the end of the home tab:

The problem is that they don’t do anything just yet. In the code that you copied back in Step 3, there were some callback items (‘CB’) that we need to reference back to inside VBA. Those are effectively a link from when a user presses the button on the ribbon to the macro that you’ve coded.

What you’ll need to do now is go into a module within VBA and enter the following code:

Sub cbMacro1(control As IRibbonControl)
***name of your macro***
End Sub

Sub cbMacro2(control As IRibbonControl)
***name of your macro***
End Sub

In the subprocedures for the two buttons all you need to do is call your macro (in place of the code within the ***), and now the buttons should work.

Modifying the code to add more customization

In Step 3, the code there was for a couple of buttons that you could add to your ribbon. If you want to add more, simply following the sequence you can easily add another button:

<button id="Macro3" label="Macro3" imageMso="Smile" size="large" onAction="cbMacro3" />

The button id doesn’t matter too much itself. The label is what will show up underneath the button. ImageMso is the image that will show up. If you do a google search for ImageMso you’ll find what some of the different codes are.

You can shrink the image down to “small” by changing the size attribute here. Note that some of the ImageMso’s are already small and can’t be made large, but you can shrink large ones down in size.

The most important item when adding a button is the onAction attribute as this is what your callback code needs to reference inside VBA. Obviously the more consistently you name your buttons the easier it’ll be to add more without getting lost in your code.

Adding a new tab

In the above code, I added the buttons to the Home tab. However, if you’ve got more that you want to add then you can put them on an entirely new tab instead.

What you can do then is just change the following:

<tab idMso="TabHome">

into this:

<tab id="MyMacros" label="My Macros">

And now all of your macros will be saved onto a new tab rather than take up space on the Home tab. You can also group your buttons based on the group code as well.

This is a small sample of what you can do to customize the ribbon in Excel. The benefits of making these changes through XML and not within Excel’s interface is that your modifications are stored within the file regardless of what computer you open it on and won’t be lost.

There is another way that you can do this using the CustomUI editor and I’ll include that in a later post.

If you liked this post on How to Customize the Ribbon in Excel Using XML, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.