Writing and adding the XML which defines a custom Microsoft Office Ribbon UI can be a time consuming and error-filled experience. A Microsoft article explains the manual process here. However, it doesn’t have to be a long aggravating procedure if you use an editor specifically designed for this work.
Note: Although I am using a Microsoft Windows Office version in this post, the ribbon can also be customized on the Mac version of Office (versions 15.17 or higher.)
The Right tool…
If you search the Web for “office ribbon editor” you will find several programs that match the criteria. Some are free others not. Some are stand-alone programs and some are Office
- RibbonX Visual Designer 2010 – an Office Add-in
- RibbonCreator 2016 – Shareware
- Custom UI Editor for Microsoft Office – Free (this is a file download link)
I used to favor the Custom UI Editor, and have used it a lot with my VBA projects. While there is no longer a support website or central distribution link, you can get it from the link above.
An updated version of the Custom UI Editor, the Office RibbonX Editor, has been released as a new open source project by Fernando Andreu. The latest version can be found here. It incorporates significant upgrades and added features that make the tool stand out amongst the others. It is now my ribbon editor of choice and I will use it for my following examples. Whichever tool you chose to use, it is much easier than opening up an Excel© file (for example) and inserting your own handwritten XML.
Understanding the Essentials…
For the sake of a tutorial, I will use the Office RibbonX Editor to open and edit the XML of a file named Blank Sample.xlsx. The workbook is just a blank file opened from Excel’s New Workbook option. What is important to understand about adding a custom ribbon tab it that the Office file must be macro-enabled if you want to add custom VBA code to the menu items. The file extension “
XML is finicky about everything; spelling, character case, spacing, keywords, and grammar (quotes, commas, and such.) The Office RibbonX Editor makes it easy to check your XML for correctness, simply click the button with the red checkmark labeled “Validate.”
The Setup…
Remember, that this is just a basic intro to using an Office ribbon XML editor. You will need to spend several hours learning the fundamentals and quirks of the Office ribbon.
The Office RibbonX Editor comes as an “exe” file that contains the program’s installer. There are several files available for download. The file named “OfficeRibbonXEditor-Installer-NETFramework,exe” will install without any additional components if your Windows system has the current .NET framework installed. Running the installer places the editor is in the “C:\Program Files (x86)\Office RibbonX Editor ” folder with a link on your start menu.
Getting Started with the XML
Use the Open command on the toolbar to open an Office document, or select a previously opened file from the File menu item. The document’s name is shown in the pane on the left of the window after it is opened (as in the following image.) An Excel workbook is used here, but you could also a Word or Access© file or any document which uses the Office Fluent interface.
After the file is opened you will need to select the type of XML which you will be using, customUI.xml (Office 2007 and later) or customUI14.xml (Office 2010 and later.) The Office 2010 custom UI schema is the latest XML for the ribbon and it’s still being used in the latest versions of Office including versions 2013, 2016, and Office 365. Which type of XML with which to work is a choice that is explained here and here. For the purpose of this short tour of the editor we will use the top selection (customUI14.xml.) I have always used the newest.
Next, select the example XML to view...Insert > Sample XML > Excel – A Custom Tab. After this selection, you will see the following text in the editor’s right window pane. It is the XML describing a custom Excel ribbon tab named “Contoso”. It is set to be inserted after the “Home” tab. The rest of the XML is standard for a custom menu, however, there are many more items that can be added, customized and defined. This Microsoft document explains the Ribbon elements and Callback Names in detail (dated 7/08/2014.)
The following screenshot shows the editor with the example XML which describes the new Ribbon tab.
Saving the Menu
Before saving the XML you should always run the XML validation routine by clicking the Validate button on the editor’s toolbar. If there is a syntax error in the XML you will see a message such as the one at the bottom of the following screenshot. Most of the time the error description is good enough for you to locate and fix the issue. The validation will not catch errors in referencing any user-defined text, such as the button id, label, or the onAction statements.
I removed an end tag from the first line in the following screenshot, ran the XML validation routine and the error message was displayed at the bottom of the window.
The error is noted as “Ln 2, Col 2: Name cannot begin with the ‘<‘ character, hexadecimal value 0x3C. Line 2, position 2.” As you can see the closing “tag” (>) is missing at the end of line 1. Though not as cryptic as with some other editors the validation error messages may take some minor detective work to find the offending issue. In this case, simply add the tag and the XML will validate.
After saving the XML, start up the workbook and the new “Contoso” tab will be visible, after the “Home” tab. Remember that the new tab will only be available on the Blank Sample.xlsx workbook. Your custom Ribbon tab is not installed application-wide within Excel but just within each workbook that contains the custom XML.
Final Details
The Contoso ribbon tab does not use any custom VBA code at this point. To have your new tab items run macros or use other “custom” VBA coded actions you will need to save the workbook as “macro-enabled” and do some programming. To simplify creating VBA sub-routines which can be called from the custom ribbon tabs items, click the Generate Callbacks button on the editor’s toolbar.
The Callback Viewer (as shown below) will open with the outline for all the required VBA sub-routines. Copy and paste the callback procedures into a code module within the document, add your own code (to execute from the onAction statement) and you have a fully functioning custom ribbon tab. Of course, this is a slight oversimplification, especially if you are not familiar with VBA or Office macros programming.
There is much more that you can do with the Ribbon UI such as making the tab item labels change to match the state of the workbook, add checkbox functions or add a custom “popup” (right-click) or Context menu. The Office RibbonX Editor has links to several very helpful resources; select from the menu Help > Useful links for a listing. You can see examples of different types of menu items by downloading and opening my free Total Cost Model workbook in the editor.
Related posts: Disabling (and enabling) Context menu items in Excel, Total Cost Model
Your explanations are great.
I have a problem with changes made in the Custom UI Editor not being saved.
Could you suggest where I could get support for solving this problem.
I don’t know if it is a software or hardware problem.
Martin Reiser
Chandler, AZ
reiser33@gmail.com
Martin,
Do you have the workbook you are working on in the Editor open? You cannot edit an open workbook and save the changes to it.
-Rick
I created one ppam add-in. I cant add that add-in in the powerpoint, getting “Sorry for some reason cant load the adding” error.
Sorry, I can not help you with that issue. I would try asking on Stack OverFlow
Hi,
I want excel on opening to position to the new command bar that is created. Excel by default shows the home menu, Is there a command either in VBA or in Office Ribbon Editor that can achieve this
Geoff,
I did have to look for this answer for a while, but I figured it out a few years ago.
This is the name of my ribbon—>Public tcmRibbonUI As IRibbonUI
CostModelTab is the name of my menu tab
I use this Onload sub to open the ribbon at my menu tab. The name of the sub must be the same as the Onload statement in the Ribbon XML (at the top). You can see how I did it better by downloading the Total Cost Model and looking at the code (including the Ribbon XML)
Public Sub OnLoad_TCM(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
Set tcmRibbonUI = ribbon
‘ set the memory pointer
Sheet_1.Range(“I3”) = ObjPtr(ribbon)
‘ open the Estimator tab at startup
tcmRibbonUI.ActivateTab (“CostModelTab”)
End Sub
Let me know if this helps,
Rick
Hi Rick. Thank you so much for this. I would not have figured it out and have not seen any mention of this on the web anywhere. I was a bit confused about the memory pointer, which is a feature of your spreadsheet, but it is not necessary for the ribbon re-direction to work.
I spent a few hours sidetracked on CustomDocumentProperties – I hadn’t come across these before and can see a couple of applications for them – they appear to be a way of storing variables without using a worksheet or declaring global variables. I keep finding new depths to Excel and VBA!
Thanks again
Thank you Geoff,
I discussed using the ribbon memory pointer in this post:
https://rlbcontractor.com/handling-office-custom-iribbonui-lost-state-errors
Like most of my work in VBA I learnt it from others.
–Rick