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.
- Excel Files as ZIP Files
- Building the Custom Ribbon
- Adding XML Relationships
- Changing our Buttons
- Full Code Example and the Steps
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
.zip to the end of the existing file name (like
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”.
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
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
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.
<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">
onLoad argument isn’t typically necessary for basic custom ribbons, but it allows the user to run a macro (in this case, the macro
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 to your email address below.
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 to your email address below.
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
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
_rels folder, and paste it outside the zipped file. Now, you can edit the
Open up 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
<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.
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
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
<button id = "button1" label = "First Button"/>
<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
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
One of the folders is meant to hold your images, so we’ll name the folder
The second folder should be called
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 (
.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
In this example, we placed a picture called
<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:
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"/>
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
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
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
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
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
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.
- Create a folder named customUI and add an xml file inside named
- Convert your
.xlsmspreadsheet to a ZIP file by adding a
.zipto the end of the file name. It’ll give a warning, but that’s okay.
- Copy the _rels folder inside the ZIP file and paste it outside the ZIP file
- Copy the below code into the
.relsfile and save.
- Copy the below code into the
my_customUI.xmlfile and save.
- Delete the _rels folder in the ZIP file.
- Copy both the new customUI folder and the modified _rels folder to the ZIP file by dragging the folders into the ZIP file.
- Convert the ZIP file back to a
- 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
<?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
<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>
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.