Adding a Button to the Ribbon menu in Excel, Word or PowerPoint is surprisingly easy. If you follow this post, you will end up with both a Button and a ComboBox (aka DropDown) looking something like this - here you can see a Group called 'Demo' which includes a Button also called 'Demo' with a custom icon and its supertip/screentip visible, plus a ComboBox in its 'collapsed' state:
To customise the Ribbon, you need to add 'RibbonX' code to the workbook / document / presentation. To do this, you will need to download the excellent (and free) Office RibbonX Editor from https://github.com/fernandreu/office-ribbonx-editor. Go to the 'Download / Build Status' section and, I suggest, download the latest release (not the latest development build).
What is this 'RibbonX'? It’s a markup 'language' that is used to create ribbon customisations. So while you code in Excel, Word or PowerPoint using VBA, you do not create ribbon customisations with VBA … you use RibbonX.
Adding a Button
Open the Office RibbonX Editor then use it to open the macro-enabled file (.xlsm, .docm, .pptm etc ... I'll just refer to this as 'the file' for the rest of this post) that you want to add a Button to. Having opened the file, you won't see much at first:
Click on, to select, the loaded file ('Book1.xlsm' in the above image but obviously this could be a document if you are working with Word or a presentation if you are working with PowerPoint) then Insert > Office 2010+ Custom UI Part. This will add a file 'customUI14.xml'.
You might ask why 'Office 2010+ Custom UI Part' and not the alternative 'Office 2007 Custom UI Part'? The answer is that I'm assuming you are using a supported version of Office (at the time of writing, that is Office 2016 or newer). The original RibbonX specification came out with Office 2007. Microsoft then updated it with Office 2010 … and has not updated it again since. As 'Office 2010+ Custom UI Part' contains more functionality, you would only use 'Office 2007 Custom UI Part' if you were creating RibbonX for use in Office 2007. And you're not.
So next, double-click on 'customUI14.xml' (or right-click it then select Open) and a tab will be added in the main window of the Office RibbonX Editor. You now have this:
Next, copy the following RibbonX code and paste it into the main window of the Office RibbonX Editor:
So now you have this:
What does all that mean? Much of it is fairly obvious but some of the more notable parts are:
The first line with '<customUI xmlns ...' defines the XML namespace being used ... it is always present and always the same when using 'Office 2010+ Custom UI Part'
The subsequent tags (and the closing equivalents at the end) indicate that we're adding a Button within a Group within a Tab within the collection of Tabs within the Ribbon
The Tab that we have added it to is a built-in Tab called "TabAddIns" - the "Mso" (MS Office) in "idMso" indicates that we are referring to something built-in (a Tab or the Ribbon in this case) rather than adding some new (in which case we would have used "id" in place of "idMso")
autoscale="True" means that if we added lots of Buttons (and/or other controls) in a Tab, then the particular Group can be re-sized to fit the available space
imageMso="HappyFace" defines the icon on the Button … see Customising the icon, below, for more on this
onAction="RibbonCallbacks.ButtonDemoOnAction" defines the VBA procedure that is called when the Button is clicked … in this case it calls a procedure called ButtonDemoOnAction() in a Module called RibbonCallbacks
screentip and supertip define the text shown when the mouse hovers over the Button
keytip defines the key(s) used to activate the Button from the keyboard following pressing the Alt key
You can view the official MS Docs to see the details of all of the available attributes for:
Anyway, having pasted in the RibbonX code, you should validate it by clicking the Validate button which will tell you (if you copy/pasted correctly) that the "Custom UI XML is well formed". Validating doesn't serve much of a purpose if you have copy/pasted the above RibbonX and not amended it at all, but is a good practice to get into when writing your own RibbonX.
Now that you know your RibbonX is valid, go ahead and click Save. You'll see a warning that "The process cannot access the file ..." if the file is also open in Excel / Word / PowerPoint (I'm going to abbreviate these to 'the Office app' for the rest of this post or I'll never get to the end of this!), if so, close the file in the app and then click Save again.
Next, click 'Generate Callbacks'. This feature generates the signatures of the procedures ('callbacks') that will be called by the RibbonX code – in this case, it is a single 'OnAction' callback for when the Button is clicked. If you have many controls with many callbacks then this feature of the Office RibbonX Editor is a bit of a life-saver! Copy the text in the 'Callbacks Viewer' window that should look like this:
Now close the 'Callbacks Viewer' window and then close Office RibbonX Editor. Open the file in the Office app. If you go to the 'Add-ins' tab of the ribbon, you will now see a 'Demo' Button (with a smiley face) in a 'Demo' group. Looks good … but does nothing (well, nothing good)! Click it and you will see a message that the 'macro cannot be found' (or similar ... the exact message is different in each app). So let's fix that.
Open the VBE (if you're a completely newbie to using the VBE and writing VBA code then I suggest you read my What is VBA post) and add a standard Module and rename it 'RibbonCallbacks'. Then within that paste the text of the callback you copied from the Office RibbonX Editor (if the callback has been lost from the clipboard, you can safely re-open the Office RibbonX Editor, open the file again and copy the callback text). Go back to the UI and click the Button again and … it does nothing. But no error message this time (if you do see the same 'macro cannot be found' message then check that the name of the Module and the name of the procedure/callback match exactly what the same names in the RibbonX ... and check that you have added the callback to a standard Module). Actually, this is good ... there is no error message because the button has successfully called the callback but we've not added any code within the callback, so head back to the VBE and add something … anything … to test the code, maybe make it tell you the time like:
Which produces (in Excel ... obviously the title will be different depending on your choice of Office app):
At this point, you can of course hook up the callback with any VBA code that you - want so the Button can do … anything.
Customising the icon
There are many built-in icons that can be used with the 'imageMso' attribute. A useful resource is https://bert-toolkit.com/imagemso-list.html ... note that the exact image varies from one version of Office to the next (they take on the overall theme that Microsoft defines which changes periodically). Perhaps a better icon to use (if you made it tell the current time) would be to use 'HeaderFooterCurrentTimeInsert' instead of 'HappyFace'. Try changing it to see (note you will have to close the file in the Office app to be able to save any edits you make to the RibbonX code in the Office RibbonX Editor).
Another option, if there is an icon already in the ribbon that you want to re-use is to go to the 'Customize the Ribbon' section of the Options (ie File > Options) in the Office app, then place the mouse pointer over any menu command in the left-hand list … after a second or two, a screentip will appear which includes (at the end, in parentheses), the imageMso name for the icon associated with that action.
However, a different approach is to use your own custom icon. Ideally, you want a .pdf or .ico file (for transparency … though you can use .jpg, .bmp and others) with (again, ideally) a size of 32x32 pixels. Ensure the file is closed in the Office app then in the Office RibbonX Editor, click to select ‘customUI14.xml’, then Insert > Icons ... select the image file you want to use as an icon. You will see the icon displayed below ‘customUI14.xml’ … you can rename it (using the Change ID right-click option) from the default that the Office RibbonX Editor uses if you want. Then amend the RibbonX to replace the line
With
Where the attribute is changed from 'imageMso' (meaning a built-in icon) to 'image' (meaning a custom icon), and the value of the attribute is the name of your icon within the Office RibbonX Editor. Validate, then save and close the Office RibbonX Editor then open the file in the Office app and marvel at the new icon (if you see a blank space where the icon should be, likely you mistyped it's name or didn't change 'imageMso' to 'image' ... if so, close the file in the Office app then go back into the Office RibbonX Editor and check the RibbonX code).
This brings me to an important point. If you did mistype the name of the icon, you'll notice that clicking Validate said that the RibbonX was fine (actually that is was 'well formed') while loading the RibbonX in the app obviously had a problem (the blank space where an icon should have been). This is because clicking Validate just checks the structure of the RibbonX, it doesn't check every aspect of it. You can make the Office app inform you of any problems with the RibbonX by going into File > Options > Advanced and ticking (checking) 'Show add-in user interface errors' (in the 'General' section, scroll down to, or very close to, the bottom). Doing this means that you, as a developer, will be informed of problems in the RibbonX. If you did type the name of the icon right and so didn't have this problem, I suggest ticking (checking) this setting anyway and then go back and make the name of the icon wrong, save, re-open the Office app to see the warning message and the blank space. Then go back and fix the icon name. As a VBA developer, I always leave this setting ticked (checked) and I suggest you do the same.
Other controls
It's not just Buttons you can add to the Ribbon. Every type of control you see in the Ribbon as standard can be added using RibbonX: ComboBoxes (aka DropDowns), CheckBoxes, EditBoxes, Galleries, SplitButtons, ToggleButtons and more can be added (see the list of 'Elements' in the menu sub-sections of section 2.2 of the MS Docs at https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/d842006e-3187-4f66-a17d-0819a3cc94b5).
To whet your appetite, as an example, here's the RibbonX for a ComboBox:
Copy this and, using the Office RibbonX Editor, paste it below the closing Button tag (the line with 'keytip') and above the line with the closing Group tag, so now you have this (the following image shows only a portion of the overall RibbonX):
As you did previously, validate the RibbonX by clicking the Validate button and, if all is okay, click Save.
Click Generate Callbacks and you will now see three procedure signatures. The first you have already used but the second for ComboBoxStuffOnChange and third for ComboBoxStuffGetText are new … copy the new two callbacks and then head back to the RibbonCallbacks module in the VBE. Note that when you open the Office app, you will see an error message that the ComboBoxStuffGetText macro could not be run. Don't worry, that's what we'll fix next ... paste the callbacks in to the RibbonCallbacks module and add some test code such as:
The ComboBoxStuffGetText callback is called once when the Office app first opens and sets the initial item visible in the ComboBox. So to make it work, close and then re-open the file in the Office app ... this time, you will see no error message (if you do, go back and check the names of the callbacks and the module in VBA match exactly those in the RibbonX and that you are using a standard Module) and the 'Red' item will be selected. Go ahead and select other items and note that you see a MsgBox when you select a new item.
While ComboBoxStuffGetText, in this case, was called only once when you first open the file in the Office app, it is possible to have it called on demand (and so you can dynamically set the visible item in the ComboBox ... and, for example, with Buttons you can dynamically enable or disable them so that when a workbook / document / presentation is open then the Button could be enabled but disabled when no workbook / document / presentation is open ... for more on this, see my post Making Ribbon controls dynamic).
Add-Ins
While this post has focused on 'normal' macro-enabled files, you can use exactly the same techniques to add RibbonX to AddIns ... obviously, then, the ribbon customisations will be available to all workbooks / documents / presentations (so long as the AddIn is loaded) in just the same way that any VBA code would be available to all workbooks / documents / presentations. However, creating AddIns is well outside the scope of this post ...!
More
There is so much more you can do with the Ribbon (add entire Tabs, customise existing Tabs, add existing Buttons to your custom Tabs, customise the 'backstage', hide all built-in Tabs and importantly, you can make it dynamic as noted just above) so much so that you could write a book about it … in fact someone did ('RibbonX: Customizing the Office 2007 Ribbon' from Wiley - https://www.wiley.com/en-us/RibbonX:+Customizing+the+Office+2007+Ribbon-p-9780470191118 … note I have no association with the authors or publisher).
The official Microsoft docs are available though not always that helpful – the docs for the original 'Office 2007 Custom UI Part' https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/31f152d6-2a5d-4b50-a867-9dbc6d01aa43 are more comprehensive, the docs for the 'Office 2010+ Custom UI Part' are here https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui2/bad56c21-7b15-41bc-af32-8b5afe6e922e.
And for a more general write-up on the Ribbon and RibbonX ("Customizing the 2007 Office Fluent Ribbon for Developers"), see: