Excel Ribbon - XML
This module provides a brief overview of:
- how to add a new Group to an existing Excel Ribbon Tab. In the example we add the
xlf Toolsgroup to the ribbon Home tab
- the XML code used in the
- the VBA Callbacks used in the
A list of Ribbon customisation techniques
- Use the File > Options > Customize Ribbon sequence (Excel 2010, 2013, and 2016).
- These are application specific (not part of the workbook)
- Easily implemented by the user
- Can be Exported / Imported with an Excel Customizations.exportedUI file
- Does not include access to XML originated workbook level customisations
- Use an XML file and add to the workbook customUI folder
- These are workbook specific (not part of the application)
- Difficult to implement for the user because XML coding is required
- Easy to maintain, and cannot easily be changed by the user
- XML code for customUI can be:
xlf Tools example
Three buttons are added as a group to the right hand end of the Home tab (figure 1).
XLF Tools group - the XML code
Links breaks do not render in this SyntaxHighter version of the XML code module. Click the code window (code 1) for the formatted version of the button items (rows 6 to 8). The addition line break code at line 7 of code 1 (line 18 of the image version) and the centre image of figure 2. is the HTML number for a carriage return (ASCII 13). This can be used in combination with , the HTML number for a line feed character (ASCII 10)
Each button in the group has different settings - see figure 2
- Fig 2: - Mouse Hover event
supertip="Pricing Models: Black-Scholes and Binomial Option types: European or American, Put or Call option" ;
screentip="Close the Active Workbook"
XLF Tools group - VBA callback
Each button has an onaction property, in this case the onaction items link to the callback macros in code 2.
Code 2: Macros for control callback
Sub GetXLver(control As IRibbonControl) Call GetXLver99 End Sub Sub OptionPricer(control As IRibbonControl) Call FrmOptpricer End Sub Sub CloseWB(control As IRibbonControl) ActiveWorkbook.Close SaveChanges:=False End Sub Private Sub GetXLver99() MsgBox "GetXLver99 placeholder" End Sub Private Sub FrmOptPricer() MsgBox "FrmOptPricer placeholder" End Sub
Creating the UI in Excel 2010 or later
Setup the xlsm file
- Example based on Excel 2010, 2013, or 2016
- Create a New blank workbook in Excel
- Open the Visual Basic Editor (VBE) and insert a new code module using the menu sequence Insert > Module
- Copy and Paste the code from code 2 above (19 lines) to the VBA module in point 2
- Save the Excel file with a .xlsm extension, named customUIdemo.xlsm in this example. Close the workbook
Create the customUI XML file
- On your Windows Desktop create a new Folder. Name it customUI.
- In NotePad++, create a new text file with file type .xml (described as eXtensible Markup Language file (*.xml; *.xsml; *.xsl; *.xsd; *.kml; *.wsdl; *.xlf; *.xliff; *.config) in NotePad++)
- Copy and paste the XML code from code 1 above.
- Save the XML file from points 6 and 7 with the name customUI14.xml and store it in the customUI folder
- (Alternative points 6 to 8) - you can download the customUI14.txt (including line break formats), change the file type extension to .xml, and then save it in the customUI folder. The xml version is also available customUI14.xml
Modify the xlsm file in a zip environment
- In Windows Explorer, change the file extension of the customUIdemo.xlsm file from .xlsm to .zip. The file is now named customUIdemo.zip
- Right click the customUI folder, then select 7-Zip > Add to archive... to display the Add to Archive dialog box
- Select ① the target archive file, Archive: customUIdemo.zip and set ② the Archive format: to zip (see figure 3), then click OK
Modify the xlsm relationships settings
- Open the customUIdemo.zip in the 7-Zip File Manager
- Select the _rels folder and open the .rels file in edit mode, then add code 3 to the Relationships (inside the relationships tags). See figure 4 and code 4 for details
XML codelocated in file .rels (_rels) folder
<Relationship Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="/customUI/customUI14.xml" Id="rId4"/>
- After adding code 3, the .rels file should look like the example in code 4
XML codelocated in /_rels/.rels file
<?xml version="1.0" encoding="utf-8"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" Id="rId3" /> <Relationship Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" Id="rId2" /> <Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" Id="rId1" /> <Relationship Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="/customUI/customUI14.xml" Id="rId4" /> </Relationships>
- Save the xml file to update the customUIdemo.zip
- Finally, change the file name extension from .zip to .xlsm Note: after the file is opened in Excel, the Relationship Id may change from Id="rId4" to something more cryptic (random) like Id="Raf2455916749499e"!
When you open the xlsm file in Microsoft Excel you should see the XLF tools custom group (figure 1). A copy of the completed customUIdemo.xlm file is available at this link [13 KB].
Microsoft, [MS-CUSTOMUI]: Custom UI XML Markup Specification Download pdf file [11,339 KB] Accessed: 25 January 2017
Microsoft, Office 2013 Help Files: Office fluent user interface control identifiers Download zip file [2,513 KB] Accessed: 25 January 2017
Alexander M and R Kusleika (2015), Excel 2016 Power Programming with VBA, Wiley, (Ch 17: Working with the Ribbon)
- This example was developed in Excel 2016 (Office 365 ProPlus).
- Revised: Tuesday 9th of October 2018 - 11:42 AM, [Australian Eastern Time (AET)]