Creating Your Own Ribbon Bar of Tools (MS Excel)

In this tutorial, we’ll walk-through how to create your own customized ribbon bar. Customized ribbon bars can bring tools you have created in Microsoft VBA to an accessible format and ease of use. They also allow you to create toolboxes of programs that can be easily used by other users, who may not be technically inclined.

The macro files used for this post are located/linked at the bottom under the GitHub section.

Access Excel Options Window

Below is a standard menu bar in MS Excel. Typically the “Developer” tab is not exposed but follow the next couple of steps and you can also make it available.

Anywhere on the menu bar you can right click and select “Customize the Ribbon…” option. This will bring up the “Excel Options” window to the “Customize the Ribbon” menu.

• 

• 

On the right pane of the window above you can turn on the “Developer” tab.

Create New Tab and Groups

First we will create a new tab by clicking the “New Tab” button. This will spawn a “New Tab (Custom)” feature in the panel immediately above the button.

Next, we name the new tab. For the current example, I have named the tab “Data Toolkit” to make data operations quickly accessible in my MS Excel application each time it is in use.

Next, we will rename the grouping. A group is just a simple way to categorize and organize your macro processes. In this example, we will create two groups, “Cleaning” and “Transformations”.

• 

• 

Assign Macros to Groups

Now that we have our groups established we can add in our desired macros. To get the list of available macros across all open MS Excel files, just above the left pane, select the drop-down and select the “Macro” option. If you don’t have any macros loaded in any open workbooks, there will be nothing. In this example, I have my “DataToolkit.xlsm” workbook open and assign the appropriate routines to their corresponding group. In the right pane, you can see this.

If you plan to keep your macro workbooks open all the time, this method is alright. But if you want something more accessible each session, follow the next section to save off your macro workbook as an Add-In.

MS Excel Add-In Method

To create an MS Excel Add-In, simply open a new workbook, import your desired macros (.bas files) and then save the file as an “Excel Add-in”. The application will try to save the file into a Microsoft Office directory. You can save the file anywhere you want though. In this example I saved the file into my “../Learning/RibbonMod/” directory. The file extension is “.xlam”.

Now, you can open the “Add-Ins” window, located in the Excel Options window. At the bottom there is a drop-down menu next to “Manage”. Make sure its on “Excel Add-Ins” and click “Go”. This will bring up a directory window to allow you to go select the desired Excel Add-In file to import. Select each of the images below for a larger detailed view.

• 

• 

• 

Once the file has been imported, make sure its checked then click “OK”. Now your macros are available each time you open MS Excel. The file doesn’t actually get loaded until you select to run a macro in the file, so it’s not always open, but certainly accessible when needed.

Now, you can replace the macros you previously assigned to the tab and groups discussed in a previous section. The image above shows what will be visible if you close out any other files with macros, aside for the recently imported Excel Add-in.

Export the Customization

After exporting the customization, you can open the file in your desired text editor. It will look pretty confusing at first. In my example, I’m using Notepad++. I then change the language to XML, which will highlight the various syntax and it will be easier to view. I tend make it more readable by putting each”<stuff>” on a new line. This will make the structure easy to view as well.

Edit the .exportedUI File

My specific example looks like this following my actions mentioned in the paragraph above.

Here is the code for your ease of use. If you use the following XML, be sure to change the file location in the “onAction” property.

<mso:cmd app="Excel" dt="1" />
<mso:customUI xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat/>
<mso:tabs>
<mso:tab id="dct" label="Data Toolkit" insertBeforeQ="mso:TabAddIns">
<mso:group id="dct_g1" label="Cleaning" autoScale="true">
<mso:button idQ="x1:dct_g1b1" label="Vertical Demerge" imageMso="FrameCreateRight" onAction="C:\...\Documents\Learning\RibbonMod\DataToolkit.xlam!Vertical_Demerge" visible="true"/>
<mso:button idQ="x1:dct_g1b2" label="Horizontal Demerge" imageMso="FrameCreateBelow" onAction="C:\...\Documents\Learning\RibbonMod\DataToolkit.xlam!Horizontal_Demerge" visible="true"/>
</mso:group>
<mso:group id="dct_g2" label="Transformations" autoScale="true">
<mso:button idQ="x1:dct_g2b1" label="Data Gathering" imageMso="MsnLogo" onAction="C:\...\Documents\Learning\RibbonMod\DataToolkit.xlam!DataGathering" visible="true"/>
<mso:button idQ="x1:dct_g2b2" label="Create Distinct Lists" imageMso="DesignXml" onAction="C:\...\Documents\Learning\RibbonMod\DataToolkit.xlam!CreateDistinct_lists" visible="true"/>
<mso:button idQ="x1:dct_g2b3" label="Look-Up (Raw -> Clean)" imageMso="DiagramRadialInsertClassic" onAction="C:\...\Documents\Learning\RibbonMod\DataToolkit.xlam!Clean_Lookup" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>
</mso:ribbon>
</mso:customUI>

Now, I begin cleaning up the file so its easier to read and not just standard output. I renamed my tab id to “dct” to symbolize data conditioning toolkit. Then using that as a prefix to each group and button ID belonging to that tab. At the group level, I add a “_g#”, where the # increments for each group. For each button in that group, the ID takes on “_g1b#”, where the # increments for each button in the group.

Now lets look at one of the buttons. They are all structured this way when you export. There is “idQ”, “label”, “imageMso”, “onAction” and “visible”. Since we set everything up in the Excel Options window, there is nothing we necessarily need to fix now. If you exported the file with the “.xlsm” file, you can change the file location and file extension here if you want to change to an add-in (.xlam).

Once you have cleaned up the file, save the file. Its now ready to re-import. You can close down Excel and re-open to start a fresh instance. Make sure you check that your Excel Add-In is actually loaded. If it is, continue on.

Importing the .exportedUI file

Here, we import the “DataToolkit.exportedUI” file through the Excel Options window described near the beginning of this post. Before importing you might want to reset all customizations to ensure you have a clean slate you are importing to.

If the file loaded successfully, you should see the tab appear with its associated groups and buttons. If the groups and buttons are not present then something is wrong with the imported file or you don’t have the Excel Add-In present. See below for example of success.

Now, your tools are ready to use in each MS Excel session.

Conclusion

If you followed the guide, you should hopefully have created your own customized tab of tools. Now that you have the resources, you can easily create more tabs, groups and buttons to support your needs and maybe your offices’ needs as well.

At a previous job, I created this for a networked drive which allowed several users to have the same tools in their MS Excel applications. When everyone imported the Excel Add-In file, they could then import the exportedUI file and run everything all the same. I was able to maintain the macros and they could have the most recent updates when they closed/opened their applications each day.

This is a workforce multiplier, especially if macros you design can save time and are used routinely.