A Quick Method for Adding Custom Ribbon Elements

Everyone knows that the days of the CommandBar are gone. Sure, you can still use the CommandBar in your VBA applications, but the result is less than spectacular. All of the entries end up on the Add-Ins tab of the Ribbon. Consequently, even though the code in Listing 12-2 of VBA for Dummies still works just fine, you end up with this addition to the Add-ins tab as a result.


Even the icon is correct from the listing, so the code does indeed work, but it isn’t as nice as it could be. Of course, you could create a true Ribbon addition for your VBA application. Starting on page 266 you discover the incredibly convoluted and horribly difficult technique to add a real addition to the Ribbon in Office 2007. The results really are quite nice and you obtain the full functionality that the Ribbon provides. However, unless you have a lot of time to spend working through these steps, you’ll quickly learn to dread the entire process.

Fortunately, Office 2010 offers a middle ground approach. The results really are Ribbon-specific and you do have some flexibility in how the buttons appear, but there are still a few limitations. Let’s look at what you need to do to obtain this middle ground result. The first phase is to create the buttons you want to use with the application and export them to a file so you can modify their appearance. Uses these steps to accomplish this task.

  1. Create your macros as normal. The example uses these two very simple macros.
    Sub SayHello1()
        MsgBox "Saying Hello From SayHello1"
    End Sub
    Sub SayHello2()
        MsgBox "Saying Hello From SayHello2."
    End Sub
  2. Attach the macros to buttons using the technique shown in my “VBA in Office 2010” post. The example uses the setup shown here:
  3. Check your results. You’ll likely see something like this where the buttons are all large and not very attractively set up:
  4. Choose File > Options to display the Options dialog box. Select the Customize Ribbon option so that you can see the Ribbon configuration again.
  5. Choose Import/Export to display the import and export options. Select Export All Customizations to display a File Save dialog box where you can save the customizations to disk.
  6. Type a name for the file, such as MyCustomization.exportedUI and click Save. At this point, you have an XML file containing your customizations.

Open the file using an XML editor or even Notepad. Any text editor will do fine. For the example, I’m using Notepad to make things a bit clearer. What you’ll see inside is going to be messy because Office won’t add any carriage returns or white space to make the text easier to read. Adding these features won’t invalidate the file. Here’s the result from the example:


Most of this code isn’t important to the example, but it’s all functional. Let’s say you want to use icons that aren’t supported directly in the Options dialog box and you also want to use medium sized icons. You can make both changes quite easily now. First change the <group> element to allow custom sizing by changing the autoScale property to read autoScale=”false”. Second, change the <mso:button> elements to look like this:

<mso:button idQ="x1:Book1_SayHello1_0_16A6B4C"
            label="Say Hello 1"
<mso:button idQ="x1:Book1_SayHello2_1_16A6B4C"
            label="Say Hello 2"

Notice that the buttons are now medium sized and are using icons that aren’t normally supported by the Excel Options dialog box. To see the results of these changes, reopen the Customize Ribbon folder of the Excel Options dialog box. Choose the Import Customization File option of the Import/Export drop down. Excel will ask whether you’re sure that you want to replace the existing Ribbon and Quick Access Toolbar customizations. Click Yes and you’ll see the changes added to your Ribbon. Close the Excel Options dialog box and you’ll see something like this:


Notice that the buttons now use different icons and they’re arranged differently. You can use this approach to create a customized look to this much simpler approach for dealing with the Ribbon. Make sure you get the Office 2010 Icon Gallery or the Office 2007 Icon Gallery to obtain usable icon names to use for your applications.

Of course, there are always limitations. So far I haven’t figured out how to use other kinds of controls. For example, I can display a toggle button just fine by changing the <mso:button> element to an <mso:toggleButton> element, but the resulting control isn’t functional. With any luck, I’ll eventually figure out techniques for working around these issues, but this post is a good start. Let me know if you try this technique out and what additions you can figure out for it at John@JohnMuellerBooks.com. You can download the example files, if desired, to see how things work.