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

Edifice 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, nosotros are going to look at decomposing an Excel file, adding our own ribbon to the top bar with custom buttons, so connecting those buttons to macros we've included in the spreadsheet itself. We volition focus on Excel, but you tin can do this with Word and PowerPoint, too.

  • Excel Files every bit Cypher Files
  • Edifice the Custom Ribbon
    • The Skeleton
    • XML NameSpace
    • Slight variations to the skeleton
    • Tabs, Groups, and Buttons
  • Adding XML Relationships
  • Changing our Buttons
    • Customizing the Buttons
      • Adding Your Ain Images or Icons
    • Connecting Macros in Callbacks
  • Full Code Case and the Steps

Fifty-fifty though this tutorial emphasize the utilize of buttons in our custom ribbon, y'all can create all kinds of objects, like dropdown menus, toggles and even image galleries.

Excel Files as Nil Files

The get-go affair we need to do is alter our .xlsm file to a ZIP file. To do this, you tin can either rename the file extension from .xlsm to .zip (like MyFileName.nix) or add a .cypher to the stop of the existing file name (similar MyFileName.xlsm.goose egg). Either manner will work.

If you lot don't have file extensions visible by default on your OS, you can enable them past clicking the View panel in any Windows binder and selecting "File proper noun extensions".

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

You lot volition likely get a warning nigh unusability if you change the file extension. Click yes since you desire to proceed. Your file will go a Zippo file, which y'all can open and explore. Inside, you should encounter folders similar _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 advise using a RibbonX XML editor. Instead, nosotros're going to directly manipulate the files using Notepad to prove you don't demand special tools to create a custom Excel VBA ribbon for your spreadsheet.

Building the Custom Ribbon

Outside the ZIP file, create a new binder chosen customUI (or whatever other proper name you desire to give it). Yous're not immune to make a new folder inside the .aught file you merely created, then you must brand it somewhere else first.

Within the customUI folder y'all just created, add a text file by right-clicking and selecting New > Text Certificate. Yous can name this file whatever you want every bit long equally at has a .xml extension. In this tutorial, I'grand going to name my file my_customUI.xml. Detect I replaced the default .txt extension with a .xml extension. This file is where we volition build the XML that controls how our new ribbon will wait. 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 department 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 up the my_customUI.xml file, correct-click it and select Open with > Notepad. The outset 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/role/2009/07/customui"              >

Apply the starting time ane if you lot're using Office 2010 or earlier, and use the second one if you lot're using 2013 or later on. In my experience, the first method works fine for newer versions of Excel, just information technology just doesn't have as many customizable features.

This tag basically tells the MS Office renderer what your tags mean later on in the document. xmlns stands for XML NameSpace. The "URL" doesn't bespeak to an actual online resource but to a schema laid out in Part's internal lawmaking. Information technology gives meaning to tags similar <ribbon> and <button>, which are function 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 kickoff exploring more than advanced ribbons, yous may observe slight modifications to the commencement couple lines in your XML ribbon. For case, you may come across where people specify the XML version on the beginning line using a line like this:

                          <?xml version="i.0" encoding="UTF-8" standalone="yes"?>

You lot 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 y'all must control things like whether certain buttons or controls on your user interface are invalidated. Yous would do this via an IRibbonUI object macro in your Excel spreadsheet, like this.

                          Public              MyRibbon              Every bit              IRibbonUI              Public              Sub              ControlRibbon              (              ribbon              Equally              IRibbonUI              )              Set              MyRibbon              =              ribbon              Terminate              Sub

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

Tabs, Groups, and Buttons

Side by side, we will add a single tab, two groups, and two buttons for each group. Just similar 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 Get-go Tab"              >              <group              id =              "group1"              characterization =              "Kickoff Group"              >              <push button              id =              "button1"              label =              "Kickoff Push button"              />              <button              id =              "button2"              characterization =              "Second Button"              />              </group>              <grouping              id =              "group2"              label =              "Second Group"              >              <button              id =              "button3"              label =              "Third Push button"              />              <button              id =              "button4"              label =              "Quaternary Button"              />              </group>              </tab>              </tabs>              </ribbon>              </customUI>

Make powerful macros with our free VBA Developer Kit

This is actually pretty bully. If y'all have trouble understanding or remembering information technology, our costless 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 Volume of Excel VBA Macros, to your email address below.

You can give the id of each control any name you lot wish, but it must not have a infinite or special character. Don't forget those forward slashes at the stop of the buttons! Without them, your XML lawmaking won't close those tags and your ribbon won't testify upwards properly.

At present that nosotros have a basic skeleton, salve your file and drop your new folder (which we named customUI) into the Cypher file you lot created before when you inverse the .xlsm extension. Only elevate and driblet your folder into the ZIP file. Before your ribbon will show upward, you lot need to add a reference, or a human relationship, to your customUI folder.

Calculation the Relationships

If you're using a RibbonX editor, this will automatically be washed for you, which is one reason many people prefer using the editors. Since nosotros're here to larn how these files collaborate without the help of an editor, nosotros'll bear witness you how to do this step manually.

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

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

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

You lot must add a new relationship that connects to your customUI binder. Become ahead and add together this line in anywhere between the Human relationship tags; preferably, just before the endmost </Relationships> tag:

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

for the 2006/01 version or

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

if y'all're using the 2009/07 version.

The Target argument should lucifer your folder name and custom XML file. Again, the Id is but a placekeeper and can be anything legal. Once you've added the line, salve the .rels file.

Side by side, you need to copy the _rels binder and the customUI binder (if you haven't already washed so) to the zipped file past dragging the folder to the zipped file. Before you can exercise that, you will need to open the ZIP file and start delete the original _rels folder. Windows won't overwrite folders within zipped files. Once yous've dragged the new folders over, yous tin can convert the .nada file back to a .xlsm past changing the file extension.

When y'all open the file, yous should have a new tab with two groups and four buttons. These buttons will take text labels, but they won't actually practise annihilation nevertheless. Go along reading to see how to customize the advent and behavior of these buttons.

Changing our Buttons

In this department, we'll explain how to customize the buttons we only 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 about powerful part of customizing ribbons.

I know nosotros just made y'all change your zipped Excel file back to a .xlsm extension, but get alee and shut your spreadsheet and change it dorsum to a .nothing file once again.

Customizing the Buttons

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

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

                          <push              id =              "button1"              label =              "First Push"              />

to

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

Once yous save the file and add the customUI folder back to your zipped spreadsheet (remember to delete the erstwhile binder in the .zip file before adding the new i), a happy confront should show upwards beside your button1. The First Button text will likewise 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 statement are typically normal or large. It's also worth mentioning that each argument is example-sensitive. That'southward tripped me upwards a couple times before!

If you want to add your own images, you tin use paradigm = "imageID" instead of imageMso = "msoID". However, you volition need to add extra relationships and include the paradigm in the Excel file. Editors make this very simple, just we're going to teach you how to do this manually, besides. Nosotros're here to learn, right?

Calculation Your Own Images or Icons

To add your own images or icons to your custom ribbon, you lot'll demand to create 2 folders within the customUI binder, which is the binder where we previously added the my_customUI.xml file. This is the folder you placed outside your zip file!

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

The 2d folder should be called _rels folder. Your customUI folder should now await like this:

Inside your customUI folder

In the _rels folder you lot just made, y'all'll desire to add one file. Pay attending now. This file should take the proper name of your XML file (my_customUI.xml for us) and take .rels added to the finish. Thus our concluding 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 yous 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 utilise to reference these pictures. You're file should wait something like this:

                          <?xml version="1.0" encoding="utf-viii"?>              <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"              />              <Human relationship              Blazon=              "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 equally you want. Each tag represents a new film in your images folder. It'southward okay to utilize 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, nosotros placed a picture show chosen my_pic_filename.png in our images folder. If nosotros want to add that image to a push on our Excel Ribbon, yous would phone call this picture past the ID nosotros specified: my_icon_1. The line to add this image to our button in the my_customUI.xml file would await similar this:

                          <button              id =              "button4"              characterization =              "Fourth Button"              prototype =              "my_icon_1"              onAction =              "sayGoodbye"              />

Notice how we changed imageMso to only image. Equally long as your relationships are set correctly and you match the ID you lot supplied in the new _rels folder, you lot will see your image in the customized Excel ribbon. All y'all accept to do is add this customUI folder back to your zipped spreadsheet past dragging information technology into the ZIP file. Don't forget to delete the quondam folder in the .nix file before adding the new one.

If y'all did something slightly wrong, there's a good chance yous'll get this warning when you commencement try to reopen your spreadsheet:

Excel Ribbon Unreadable Content

Simply press "Yes" and Excel will attempt to clean up the syntax in your XML files. Assuming you lot didn't mess upward too bad, Excel should successfully recover your file and your custom ribbon should appear. Simply save your file once more and you'll be back in business.

Connecting the Macros

Up until now, we've merely 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 =              "Start Button"              imageMso =              "HappyFace"              size =              "large"              onAction =              "getName"              />

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

When yous convert the Naught file dorsum to an Excel file, brand sure to salvage it every bit the original type (.xlsm in our case). If necessary, you tin can catechumen information technology from .xlsx to .xlsm afterwards through Excel itself.

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

For case, for the getName callback in a higher place, 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-minded, yous will receive an error.

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

                          Public              Sub              ProcessRibbon              (              Control              As              IRibbonControl              )              Select              Instance              Command              .              ID              'call different macro based on push 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 represent to the push IDs we specified in our my_customUI.xml file. Depending on which push is pressed, a new macro is launched. Simply replace the module name and macro proper noun with the public sub you want to launch. If you'd rather your subs be private, call back there are several ways to call a private sub from another module.

The advantage of structuring your macro this way is that you simply need the (Control Equally IRibbonControl) code in one case. In other words, you but 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 lot can ameliorate understand and fully appreciate how Excel files are built.

If you prefer using an editor like RibbonX, yous can easily build custom ribbons automatically, and y'all won't have to worry virtually choosing relationships, changing spreadsheets to Aught files, and manipulating controls. By understanding how to practice all this manually, you lot're better able to hands deconstruct whatever an editor does to build your own sophisticated user interfaces. This section will summarize the steps, with a total instance to get you on your mode.

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, just that'south okay.
  3. Copy the _rels folder within the Nix file and paste information technology outside the Nix file
  4. Copy the below code into the .rels file and save.
  5. Copy the below lawmaking into the my_customUI.xml file and save.
  6. Delete the _rels folder in the ZIP file.
  7. Copy both the new customUI binder and the modified _rels folder to the ZIP file by dragging the folders into the ZIP file.
  8. Convert the Nil 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 Equally IRibbonControl) argument nosotros talked about earlier.

Place this lawmaking in the .rels file 

                          <?xml version="i.0" encoding="UTF-eight" standalone="yeah"?>              <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/cadre-backdrop"              Target=              "docProps/cadre.xml"              />              <Human relationship              Id=              "rId1"              Blazon=              "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"              characterization =              "Showtime Group"              >              <button              id =              "button1"              characterization =              "Outset Push button"              imageMso =              "HappyFace"              size =              "big"              onAction =              "getName"              />              <button              id =              "button2"              characterization =              "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 Push button"              imageMso =              "SadFace"              onAction =              "sayGoodbye"              />              </group>              </tab>              </tabs>              </ribbon>              </customUI>

Terminal Output:

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

Y'all'll notice that some icons are blurrier than others. That's because not all mso images are designed to support the big button fashion. To create your own ain custom images, but alter the steps above with our guidance on adding your own images to your custom ribbon.

That'southward all for this tutorial. When you lot're set up to take your VBA to the side by side level, subscribe using the course below.