Creating a Custom Ribbon Tools in Excel
The User Interface Editor (UI Editor) Allows you to edit the Ribbon using XML. This is extremely useful when creating a Custom Ribbon Tab, Groups & Buttons. You can even limit what can be accessed on the Ribbon for certain Excel Files. This Tool is very useful for developers.
Download and Install the RibbonX UI Editor
Keeping in mind, that you will need to update your .NET Core SDK.
Your application will notify if it is not up to date and direct to the relevant Microsoft Page, the following link will direct you to the file.
Once on the page you will be directed to the correct Installer. Most Windows Machines are x64 these days, but check your windows settings.
After you have installed the update, it will then allow the UI Editor to load.
Keep your Microsoft Excel File Closed
In order for you to Edit the Ribbon for a Specific File, it will need to be closed. The XML Code is stored in the file.
Then Start your UI Editor then File > Open > FileName.xlsm
Once the File is Opened, you can then add some sample XML Code to your code window.
To do this Click on Insert > Sample XML > Excel – A Custom Tab
The above Code will now be displayed. What it will do is create a Table just after your home Tab.
You will notice there are some indicators as to the Tab name and Position. The code is in some way similar to HTML, With TAB being the main section, then Groups under the TAB and Buttons within Groups.
- Id – Actual Object Designation
- Label – Is what the User Sees
- Size – The Button Size
- ImageMso – Microsoft Designated Image Name
- OnAction – Action Name for the Microsoft Tool or Macro
In Our Excel VBA Course we cover all of These to help you create a Tab that is work specific.
First Rule of XML is text is Case Sensitive, even for Macro Names.
For this Tutorial we will focus on some basic changes to the Ribbon.
Firstly lets move the Ribbon Tab Contoso Tools to After the Help Tab.
To do that we change the Code from TabHome to TabHelp
Once we have completed that we will now make some other adjustments to the Ribbon.
For the Name Name and Id I have changed it from Contoso Tools to the following:
id=”davidTab” label=”David’s Tab”
You can use any name you desire, between the quotes.
Another adjustment I will make, is the Group Name
label=”My Amazing Group Name”
For this I decided to use just customGroup1 for my Id and my website as the group name. (The Group name should provide an apt description of the tools in that group.)
For the last part of this tutorial, I will focus on one of the buttons, I would like to change.
I would like add a print button. So I have adjusted the following:
The button id, renamed to “myprintButton”, label to “Print Something” and my ImageMso to “FilePrint”
Note: Microsoft has specific names for each of it buttons, I found the following website handy.
http://bert-toolkit.com/imagemso-list.html or you can download the control identifiers as an Excel Worksheet. https://www.microsoft.com/en-us/download/details.aspx?id=6627
Gives you a list of all of the Microsoft button images you can use. What you may find easier is to use your own images.
Assigning to Macro
For this tutorial we will assume you are already using Macros in Excel, so we will use a simple print Macro to Illustrate.
In order to run the Macro from the ribbon Button we need to associate the button with the macro itself.
Firstly we need to correct name and case of the Macro. Our Macro for this example is PrintStuff
In the VBE it will look something like this.
Once you are sure of the correct Macro name, you must close the Excel File. Then you need to reopen the UI Editor.
In the Code Editor you can then add your Macro Name.
Warning: You are not finished yet
So the question is how to associate the Macro you have with the Button in your ribbon.
To do that we need to use the general call back feature in the UI Editor.
The above code control As IribbonControl must be pasted into your bracket () section of your Macro.
So save and close your UI Editor and Reopen your Excel File.
Open up your VBE, and View your Module with your Macro, you would like to associate.
Once this is completed, you have successfully associated the Macro with a Button on your new Custom Ribbon Tab.
For further information or assistance, contact us on firstname.lastname@example.org