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 Tools group to the ribbon Home tab
• the XML code used in the xlf Tools example
• the VBA Callbacks used in the xlf Tools example

Custom ribbon

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:
  • Written manually, as in this example, using a text editor (NotePad++) and zip file editor (7-Zip)
  • Written using packages such as the Custom UI Editor, the RibbonX Visual Designer, or VSTO.

The 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

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.

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

1. Example based on Excel 2010, 2013, or 2016

1. Create a New blank workbook in Excel
2. Open the Visual Basic Editor (VBE) and insert a new code module using the menu sequence Insert > Module
3. Copy and Paste the code from code 2 above (19 lines) to the VBA module in point 2
4. Save the Excel file with a .xlsm extension, named customUIdemo.xlsm in this example. Close the workbook

Create the customUI XML file

5. On your Windows Desktop create a new Folder. Name it customUI.
6. 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++)
7. Copy and paste the XML code from code 1 above.
8. Save the XML file from points 6 and 7 with the name customUI14.xml and store it in the customUI folder
9. (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

9. In Windows Explorer, change the file extension of the customUIdemo.xlsm file from .xlsm to .zip. The file is now named customUIdemo.zip
10. Right click the customUI folder, then select 7-Zip > Add to archive... to display the Add to Archive dialog box
11. 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

12. Open the customUIdemo.zip in the 7-Zip File Manager
13. 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

 <Relationship  Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="/customUI/customUI14.xml" Id="rId4"/>

14. After adding code 3, the .rels file should look like the example in code 4

<?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>

15. Save the xml file to update the customUIdemo.zip
16. 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].

References

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: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy