Excel Ribbon - XML


This module provides a brief overview of:

Custom ribbon


A list of Ribbon customisation techniques


The xlf Tools example


Three buttons are added as a group to the right hand end of the Home tab (figure 1).

xlf-tools-ribbon
Fig 1: the custom XLF Tools group on the Home tab - contains three buttons with labels: xlVersion, Option Pricer, and Close

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


supertip
    Fig 2: - Mouse Hover event -
  • (left image) label="xlVersion" ;
  • (centre image) label="Option Pricer", supertip="Pricing Models: Black-Scholes and Binomial 
Option types: European or American, Put or Call option" ;
  • (right image) 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

  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

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

  1. In Windows Explorer, change the file extension of the customUIdemo.xlsm file from .xlsm to .zip. The file is now named customUIdemo.zip
  2. Right click the customUI folder, then select 7-Zip > Add to archive... to display the Add to Archive dialog box
  3. Select the target archive file, Archive: customUIdemo.zip and set the Archive format: to zip (see figure 3), then click OK
xlf-add-to-archive
Fig 3: the Add to Archive dialog box: select the target zip file, Archive format: zip

Modify the xlsm relationships settings

  1. Open the customUIdemo.zip in the 7-Zip File Manager
  2. 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
xlf-customui-rels
Fig 4: the _rels folder - click to locate and open the .rels file

Code 3: XML code located in file .rels (_rels) folder
 <Relationship  Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="/customUI/customUI14.xml" Id="rId4"/>


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

Code 4: XML code located 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>

  1. Save the xml file to update the customUIdemo.zip
  2. 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)