You can hate it, or you can love it, but it’s there… on top. If you’re stuck with Microsoft Office 2007, or using a bit polished 2010, maybe a shiny 2013, you have to use it. The Ribbon interface: pure office experience with big and colorful icons. Intuitive and open standard-based. And it leaves room for customizations, too! When already in Microsoft heaven, let’s see some hints on how create a custom tab with a couple of buttons.
The basic idea is that if you have an MS Office Open XML document with some VBA routines, then you can provide a user interface for those routines by adding custom controls to the Ribbon. This custom interface is actually embedded in the document, but you need external tools to create it. In fact you need a plain text editor and something to unzip your document, which can be Windows itself, when you fake the filename extension.
Having our document unzipped, we’ll see the folder structure which is ready to hold our customizations.
- First of all, we create a folder in the root to contain elements of our custom UI. So let’s call it customUI.
- We add a “Relationship” element to \_rels\.rels.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/> <Relationship Id="rIdCustom" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/ribbon.xml"/> <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/> </Relationships>
The Target attribute is the relative path of our UI’s configuration file to be, for which we invent a name, like ribbon.xml.
- Now we create the configuration file.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab id="actionsTabID" label="Tab for Actions"> <group id="actionsID" label="Actions"> <button id="action1ID" label="First action" size="large" onAction="ThisWorkbook.ActionHandler" image="Action1ImageID" /> <button id="action2ID" label="Second action" size="large" onAction="ThisWorkbook.ActionHandler" image="Action2ImageID" /> </group > </tab> </tabs> </ribbon> </customUI>
Our root XML element’s descendants are obviously related to parts of the user interface, and have some common attributes (id is for references in code and label is the text to be displayed).
Custom button images can make a nice impression, but they are also advised when the solution is intended for multiple Office versions. System icons are available, you can add them in imageMso attributes, if you know the right IDs. They are to be found in the official Icons Gallery, but it’s quite an effort to browse through.
What’s rather interesting to us now is the onAction attribute. This is where to add the name of the VBA routine we want to call, when the user clicks the button.
- If we want to use our own images, then we shall copy those somewhere inside the customUI folder and define their relations. Say, we copied action_one.png and action_two.png into \customUI\images, then we need a relations file \customUI\_rels\ribbon.xml.rels like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="Action1ImageID" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/action_one.png"/> <Relationship Id="Action2ImageID" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/action_two.png"/> </Relationships>
Relationship IDs are the same, as we used in our ribbon.xml file as image attributes.
- After repacking and opening the document, we still need to write the routines mentioned. Only then will we see our custom tab on the Ribbon.
- So let’s define a Sub which handles click events. Reference to the control is passed as an argument, so we can determine which button is involved.
Public Sub HandleEvent(Optional Caller As IRibbonControl) Select Case Caller.ID Case "Action1Id" ' perform action one MsgBox "Action One" Case "Action2Id" ' perform action two MsgBox "Action Two" End Select End Sub
- Now, let’s see the result (in an Excel workbook).
- It is possible to execute code when the custom UI is loaded. We use this to activate the custom tab just after opening the document. We add the onLoad attribute to the root element in ribbon.xml.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="ThisWorkbook.CustomUIinit">
The CustomUIInit routine looks like this:
Public Sub CustomUIinit(CustomRibbon As IRibbonUI) On Error Resume Next CustomRibbon.ActivateTab "actionsTabID" End Sub
Unfortunately ActivateTab method isn’t available in Office 2007, that’s why an On Error statement was added.