Did you know that every Microsoft Office file (.docx, .xlsx, .pptx) is actually a ZIP file containing XML files that build the final document, spreadsheet, or presentation? By accessing this XML file structure, we can build our own custom ribbons and link buttons on those ribbons to macros we write ourselves.

Building a custom ribbon creates a highly professional appearance and is great if you want to package your project as an add-in. As a matter of fact, this is exactly how I made the custom ribbons in my Mouse to Macro and wellsrPRO Excel add-ins.

In this tutorial, we are going to look at decomposing an Excel file, adding our own ribbon to the top bar with custom buttons, then connecting those buttons to macros we’ve included in the spreadsheet itself. We will focus on Excel, but you can do this with Word and PowerPoint, too.

Even though this tutorial emphasize the use of buttons in our custom ribbon, you can create all kinds of objects, like dropdown menus, toggles and even image galleries.

Excel Files as ZIP Files

The first thing we need to do is change our .xlsm file to a ZIP file. To do this, you can either rename the file extension from .xlsm to .zip (like MyFileName.zip) or add a .zip to the end of the existing file name (like MyFileName.xlsm.zip). Either way will work.

If you don’t have file extensions visible by default on your OS, you can enable them by clicking the View panel in any Windows folder and selecting “File name extensions”.

Folder with View panel open and a change of file extension
The red boxes highlight the buttons to make file extensions visible. Notice we changed the extension to .zip in the green box

You will likely get a warning about unusability if you change the file extension. Click yes since you want to proceed. Your file will become a ZIP file, which you can open and explore. Inside, you should see folders like _rels, docProps, and xl. To create our custom Excel ribbon, we will be creating a new folder and making some slight changes inside the existing _rels folder.

Most tutorials suggest using a RibbonX XML editor. Instead, we’re going to directly manipulate the files using Notepad to prove you don’t need special tools to create a custom Excel VBA ribbon for your spreadsheet.

Building the Custom Ribbon

Outside the ZIP file, create a new folder called customUI (or whatever other name you want to give it). You’re not allowed to make a new folder inside the .zip file you just created, so you must make it somewhere else first.

Inside the customUI folder you just created, add a text file by right-clicking and selecting New > Text Document. You can name this file whatever you want as long as at has a .xml extension. In this tutorial, I’m going to name my file my_customUI.xml. Notice I replaced the default .txt extension with a .xml extension. This file is where we will build the XML that controls how our new ribbon will look. This file controls the user interface of our custom ribbon.

The Skeleton

XML files are very similar to HTML, and the syntax looks almost identical. You will need to open each section with a pair of angle brackets <> and close them with a similar pair of brackets with a forward slash </>.

The XML NameSpace Line

To open the my_customUI.xml file, right-click it and select Open with > Notepad. The first line in our new xml file should be:

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

or

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

Use the first one if you’re using Office 2010 or earlier, and use the second one if you’re using 2013 or later. In my experience, the first method works fine for newer versions of Excel, but it just doesn’t have as many customizable features.

This tag basically tells the MS Office renderer what your tags mean later in the document. xmlns stands for XML NameSpace. The “URL” doesn’t point to an actual online resource but to a schema laid out in Office’s internal code. It gives meaning to tags like <ribbon> and <button>, which are part of the skeleton. The 2013 and newer versions offer additional features not available in earlier versions of Excel.

Slight variations to the skeleton

As you start exploring more advanced ribbons, you may discover slight modifications to the first couple lines in your XML ribbon. For example, you may see where people specify the XML version on the first line using a line like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

You might also see where people specify an onLoad argument in their <customUI> tag, like this:

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

Specifying an onLoad argument isn’t typically necessary for basic custom ribbons, but it allows the user to run a macro (in this case, the macro ControlRibbon) each time the ribbon is loaded. This is important if you must control things like whether certain buttons or controls on your user interface are invalidated. You would do this via an IRibbonUI object macro in your Excel spreadsheet, like this.

Public MyRibbon As IRibbonUI

Public Sub ControlRibbon(ribbon As IRibbonUI)
    Set MyRibbon = ribbon
End Sub

Again, this isn’t required for most custom ribbons. I just wanted to point these options out in case you stumbled across them while learning more about advanced Excel ribbon customizations using VBA.

Tabs, Groups, and Buttons

Next, we will add a single tab, two groups, and two buttons for each group. Just like an HTML document, XML documents allow nested grouping, which allows us to build multiple parts.

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

<tab id = "first_tab" label = "Our First Tab">
	<group id = "group1" label = "First Group">
		<button id = "button1" label = "First Button"/>
		<button id = "button2" label = "Second Button"/>
	</group>
	
	<group id = "group2" label = "Second Group">
		<button id = "button3" label =  "Third Button"/>
		<button id = "button4" label = "Fourth Button"/>
	</group>
</tab>

</tabs>
</ribbon>
</customUI>

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

You can give the id of each control any name you wish, but it must not have a space or special character. Don’t forget those forward slashes at the end of the buttons! Without them, your XML code won’t close those tags and your ribbon won’t show up properly.

Now that we have a basic skeleton, save your file and drop your new folder (which we named customUI) into the ZIP file you created earlier when you changed the .xlsm extension. Simply drag and drop your folder into the ZIP file. Before your ribbon will show up, you need to add a reference, or a relationship, to your customUI folder.

Adding the Relationships

If you’re using a RibbonX editor, this will automatically be done for you, which is one reason many people prefer using the editors. Since we’re here to learn how these files interact without the help of an editor, we’ll show you how to do this step manually.

Using the built-in Windows tools, you won’t be able to add or manipulate individual files inside a zipped file. Instead, you should enter the zipped Excel Ribbon.zip folder, copy the _rels folder, and paste it outside the zipped file. Now, you can edit the .rels XML file inside.

Open up the .rels file inside the _rels folder using Notepad. You should see something like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
	<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
. 
.
.
</Relationships>

You must add a new relationship that connects to your customUI folder. Go ahead and add this line in anywhere between the Relationship tags; preferably, just before the closing </Relationships> tag:

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

for the 2006/01 version or

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

if you’re using the 2009/07 version.

The Target argument should match your folder name and custom XML file. Again, the Id is just a placekeeper and can be anything legal. Once you’ve added the line, save the .rels file.

Next, you need to copy the _rels folder and the customUI folder (if you haven’t already done so) to the zipped file by dragging the folder to the zipped file. Before you can do that, you will need to open the ZIP file and first delete the original _rels folder. Windows won’t overwrite folders inside zipped files. Once you’ve dragged the new folders over, you can convert the .zip file back to a .xlsm by changing the file extension.

When you open the file, you should have a new tab with two groups and four buttons. These buttons will have text labels, but they won’t actually do anything yet. Keep reading to see how to customize the appearance and behavior of these buttons.

Changing our Buttons

In this section, we’ll explain how to customize the buttons we just made. Specifically, we’ll describe how to display images on our buttons and link them to macros in our spreadsheet. Connecting macros to your own buttons and ribbons is the most powerful part of customizing ribbons.

I know we just made you change your zipped Excel file back to a .xlsm extension, but go ahead and close your spreadsheet and change it back to a .zip file again.

Customizing the Buttons

In the skeleton section, notice how we set the label of the buttons. The label argument controls the text that appears beside each button. The label argument isn’t the only argument there is, though. You can use other arguments to control which macros run when the button is pressed and you can even add icons from the massive catalog built directly into the operating system. You can also add your own images, if you’d like.

Since the icons are already built in, you can reference them right away. In our unzipped my_customUI.xml file, change the line

<button id = "button1" label = "First Button"/>

to

<button id = "button1" label = "First Button" imageMso = "HappyFace" size = "large"/>

Once you save the file and add the customUI folder back to your zipped spreadsheet (remember to delete the old folder in the .zip file before adding the new one), a happy face should show up beside your button1. The First Button text will also still be there. You can find lists of all the imageMso icons online. Just search “msoimage” or a similar phrase.

The acceptable values for the size argument are typically normal or large. It’s also worth mentioning that each argument is case-sensitive. That’s tripped me up a couple times before!

If you want to add your own images, you can use image = "imageID" instead of imageMso = "msoID". However, you will need to add extra relationships and include the image in the Excel file. Editors make this very simple, but we’re going to teach you how to do this manually, as well. We’re here to learn, right?

Adding Your Own Images or Icons

To add your own images or icons to your custom ribbon, you’ll need to create two folders inside the customUI folder, which is the folder where we previously added the my_customUI.xml file. This is the folder you placed outside your zip file!

One of the folders is meant to hold your images, so we’ll name the folder images. In this folder, you just need to add the picture file you want to use and give each file a unique name. I typically use .png files with dimensions of 48x48, but there’s nothing magical about this. You just don’t want them too small or they’ll be blurry.

The second folder should be called _rels folder. Your customUI folder should now look like this:

Inside your customUI folder

In the _rels folder you just made, you’ll want to add one file. Pay attention now. This file should take the name of your XML file (my_customUI.xml for us) and have .rels added to the end. Thus our final filename for the sole file in this new _rels folder should be my_customUI.xml.rels.

This .rels file will tell Excel how to identify the images you want to put on your ribbon. It will contain a relationships tag with the filename of each of our images and an ID we’ll use to reference these pictures. You’re file should look something like this:

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/my_pic_filename.png" Id="my_icon_1" />
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/my_2nd_pic_filename.png" Id="my_icon_2" />

</Relationships>

You can have as many Relationship tags as you want. Each tag represents a new picture in your images folder. It’s okay to use the 2006 version of the schema type for images, even if you are using the 2009 version for the main XML file you created earlier.

In this example, we placed a picture called my_pic_filename.png in our images folder. If we want to add that image to a button on our Excel Ribbon, you would call this picture by the ID we specified: my_icon_1. The line to add this image to our button in the my_customUI.xml file would look like this:

<button id = "button4" label = "Fourth Button" image = "my_icon_1" onAction = "sayGoodbye"/>

Notice how we changed imageMso to just image. As long as your relationships are set up correctly and you match the ID you supplied in the new _rels folder, you will see your image in the customized Excel ribbon. All you have to do is add this customUI folder back to your zipped spreadsheet by dragging it into the ZIP file. Don’t forget to delete the old folder in the .zip file before adding the new one.

If you did something slightly wrong, there’s a good chance you’ll get this warning when you first try to reopen your spreadsheet:

Excel Ribbon Unreadable Content

Just press “Yes” and Excel will attempt to clean up the syntax in your XML files. Assuming you didn’t mess up too bad, Excel should successfully recover your file and your custom ribbon should appear. Simply save your file again and you’ll be back in business.

Connecting the Macros

Up until now, we’ve only built the user interface for our custom ribbon. We haven’t actually told Excel how to handle these buttons. To do that, we need to connect them to a macro.

The code for connecting to a macro is simple: onAction = "macro_name". That’s it. Place this inside the button tags, like so:

<button id = "button1" label = "First Button" imageMso = "HappyFace" size = "large" onAction = "getName"/>

Save your my_customUI.xml file, delete the old customUI folder inside your ZIP file if it exists, then add your new customUI folder back to the zip file.

When you convert the ZIP file back to an Excel file, make sure to save it as the original type (.xlsm in our example). If necessary, you can convert it from .xlsx to .xlsm later through Excel itself.

These onAction connections are termed Callbacks, and you will need to include a little extra piece in their associated macros for them to work properly with the new UI.

For example, for the getName callback above, you might have a macro like this:

Sub getName(control As IRibbonControl)
'sample macro that's called with the 
'onAction callback to the getName macro.
un = InputBox("Enter Name")
MsgBox ("Hello " & un)
End Sub

The control As IRibbonControl is required for the connection to work. If it is absent, you will receive an error.

Some people prefer to have all their buttons handled by a single macro. In that case, you would point each of the onAction arguments to the same macro, like ProcessRibbon. Then, your ProcessRibbon macro would call separate macros depending on which button was pressed, like this:

Public Sub ProcessRibbon(Control As IRibbonControl)
    Select Case Control.ID
        'call different macro based on button name pressed
        Case "button1"
            Module1.MyFirstMacro
        Case "button2"
            Module1.MySecondMacro
        Case "button3"
            Module2.MyThirdMacro
        Case "button4"
            Module2.MyFourthMacro
    End Select
End Sub

The Case names correspond to the button IDs we specified in our my_customUI.xml file. Depending on which button is pressed, a new macro is launched. Simply replace the module name and macro name with the public sub you want to launch. If you’d rather your subs be private, remember there are several ways to call a private sub from another module.

The advantage of structuring your macro this way is that you only need the (Control As IRibbonControl) code once. In other words, you only need to include it in your ProcessRibbon macro and not in the other macros you call from that parent macro.

A Full customUI XML File and Steps

We covered a lot of ground in this tutorial. Most if it wasn’t even VBA, since custom ribbon designs are controlled by XML. We did all this manually so you can better understand and fully appreciate how Excel files are built.

If you prefer using an editor like RibbonX, you can easily build custom ribbons automatically, and you won’t have to worry about choosing relationships, changing spreadsheets to ZIP files, and manipulating controls. By understanding how to do all this manually, you’re better able to easily deconstruct whatever an editor does to build your own sophisticated user interfaces. This section will summarize the steps, with a full example to get you on your way.

Steps

  1. Create a folder named customUI and add an xml file inside named my_customUI.xml.
  2. Convert your .xlsm spreadsheet to a ZIP file by adding a .zip to the end of the file name. It’ll give a warning, but that’s okay.
  3. Copy the _rels folder inside the ZIP file and paste it outside the ZIP file
  4. Copy the below code into the .rels file and save.
  5. Copy the below code into the my_customUI.xml file and save.
  6. Delete the _rels folder in the ZIP file.
  7. Copy both the new customUI folder and the modified _rels folder to the ZIP file by dragging the folders into the ZIP file.
  8. Convert the ZIP file back to a .xlsm file.
  9. Ensure you macros are callable from the buttons. Basically, just make sure they exist in a module in your spreadsheet and have the (Control As IRibbonControl) argument we talked about earlier.

Place this code in the .rels file

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
	<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
	<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
	<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
	<Relationship Id="xyzabc123" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/my_customUI.xml"/>
</Relationships>

Place this code in the my_customUI.xml file

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

<tab id = "first_tab" label = "Our First Tab">
	<group id = "group1" label = "First Group">
		<button id = "button1" label = "First Button" imageMso = "HappyFace" size = "large" onAction = "getName"/>
		<button id = "button2" label = "Second Button" imageMso = "ContextHelp" size = "large" onAction ="giveHelp"/>
	</group>
	
	<group id = "group2" label = "Second Group">
		<button id = "button3" label =  "Third Button" imageMso = "MicrosoftPowerPoint" onAction="generatePPT"/>
		<button id = "button4" label = "Fourth Button" imageMso = "SadFace" onAction = "sayGoodbye"/>
	</group>
</tab>

</tabs>
</ribbon>
</customUI>

Final Output:

New Tab with Two Groups and Two Buttons
The Final ribbon with new groups, buttons, and icons

You’ll notice that some icons are blurrier than others. That’s because not all mso images are designed to support the large button style. To create your own own custom images, simply modify the steps above with our guidance on adding your own images to your custom ribbon.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.