Customizing the Excel Ribbon using XML
Last week, I wrote a blog post discussing the benefits of XML files, stating that there were few benefits to Excel's standard XLSX/XLSM file formats. However, there is one key benefit of using an XML file format and that's that you can create your own menus within a file.
Most the time you can customize Excel using VBA macros. However, since Excel 2007, you have been very limited in your ability to customize Excel's menus if you stick to Visual Basic. In fact, you can only add regular buttons to a single group e.g. Clipboard/Font/Alignment, on a single tab e.g. Home of the ribbon. You can insert your own images from elsewhere in your spreadsheet using the 'PasteFace' command for your selected Control...but you can only have a small 16x16 image, so there may be little point.
So you don't write a macro. Instead you use a completely new program to create ribbons directly into the Excel file's XML source code. You will need to download this Microsoft Office XML editor. It's linked from the Microsoft's own documentation so it's safe. You should then install it, and open the spreadsheet into which you wish to place a new custom ribbon. You can then select to 'Insert Office 2007/2010 Custom UI Part' from the navigation. Then you can write some code e.g.
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Custom Ribbon" insertAfterMso="TabView">
<group id="highlights" label="Highlight Options"_> <_button id="customButton1" label="Highlight Red" onAction="HighlightRed" image="highlightred" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
The above will add a single button that is intended to add red highlighting to a cell. The image reference is to an image "highlightred.png" (it's a red box) that you can upload by selecting to Insert->Icons. It's really not difficult to use. If you do a web search you can find everything you want to know e.g. how to make dropdown menus, how to make large buttons etc. etc.
What isn't quite so well documented is how you then make your button do something; the button triggers a macro called "HighlightRed" that is embedded in your spreadsheet, but the way in which it's embedded becomes very important. Here is the correct grammar for the sub-routine containing the code:
Public Sub HighlightRed(control As IRibbonControl)
Selection.Font.ColorIndex = 3
End Sub
We now have a menu bar that appears whenever you open the Excel file and can highlight cells red. If you wanted your custom menu to be permanently available, you can make your XML edits to a blank workbook saved as an Excel add-in (xlam format). You would ensure the add-in auto-starts with Excel, so always giving you access to the menu.
If you would like to develop custom menus but are struggling to adapt the above code, you can always contact our expert Excel consultants and they will be happy to help; it's one of the more challenging tasks we get asked to complete
