Carrying on from the previous post …
The first thing is to think of some example facilities that will be useful independently of a particular workbook structure. Some ‘smart’ navigation, perhaps:
- Pick up the value of the currently selected cell, treat it as a range name, and go to that range. This could be useful where we have lots of data values that reference people, locations, parts, etc, and elsewhere in the workbook we have definitions or further information about those entities, with the definitions being named accordingly. Having looked up a definition, we then want to go back to the original reference.
- Create, go to and delete ‘bookmarks’ within our workbook. Bookmarks could well be temporary, and should be distinct from the standard range names.
- In both cases, we’d like some user interface other than the Name Box drop-down.
Here’s what it might look like:
This is a custom tab (called Navigation), with four buttons and a combo box. The extensions could have been added to a built-in tab, instead.
The key difference from earlier versions of Excel (Word, etc) is that the configuration for the ribbon extensions is specified in an XML file that lives within the document ‘file’ (.xlsm, in our case). This is really a zipped archive, which you can open; when I refer to ‘archive’, this is what I mean. The XML file is customUI\customUI.xml (create the folder initially). Here’s the configuration with the Definition controls:
<?xml version="1.0" encoding="UTF-8"?> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="RibbonLoad"> <ribbon> <tabs> <tab id="NavTab" label="Navigation"> <group id="LinkGroup" label="Link"> <button id="DefinitionButton" imageMso="Info" size="large" label="Definition" onAction="Definition.GoToName" /> <button id="BackButton" imageMso="ScreenNavigatorBack" size="large" label="Back" onAction="Definition.GoBack" /> </group> </tab> </tabs> </ribbon> </customUI>
The hierarchical structure of ribbon, tabs, groups, controls is fairly clear.
The button images are standard MS ones, of which there are many. Ron de Bruin has pointers to information on these (see his Section 5).
Note the onAction attributes, which are callbacks to procedures in our VBA code, of the form Module.Procedure.
Names of attributes and built-in entities are case-sensitive, unlike VBA code – beware! Errors in the XML configuration result in the extensions not loading, with no error messages or other indication (unless I’m missing something).
In order to get the customUI extensions picked up by the application, you need to add a declaration in the archive file _rels\.rels:
<Relationship Id="customUIRelID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>
Once added, this does not need to change.
Incidentally, I’m putting this in an xlsm document, but it would in practice go in an xlam (add-in).
We now need to put the callback procedures somewhere in our VBA project. The module doesn’t matter, but must be the one named in the ribbon configuration: Definition.
Dim source As Range 'module level Sub GoToName(ctrl As IRibbonControl) On Error Resume Next 'ignore if name not defined Dim val As String Dim name As String Set source = Selection val = source.Value name = ActiveWorkbook.Names(val).name Application.Goto Reference:=name End Sub Sub GoBack(ctrl As IRibbonControl) On Error Resume Next Application.Goto Reference:=source End Sub
Note the standard parameter to control callbacks, of type IRibbonControl. From this reference you can get the control Id (as in the configuration). Casing on this would allow you to use the same callback procedure for multiple controls. You can also get the Context, which in this case at least is the Window.
The alert reader (I love saying that) might have spotted a callback in the root <customUI> element in the configuration:
This allows our VBA to get and hold on to a reference to the ribbon itself. We’ll need this in order to change the contents of our combo-box dynamically (see next installment).
Now, if this all seems a bit of a performance, you might have a point. If you want a bit of help with the XML editing, and updating the document archive, there is a simple helper application, the Custom UI Editor. Personally, I’d rather use a proper XML editor, and just drag-and-drop files into the archive. Note that you can’t have the document open as both an archive and an Excel workbook at the same time – unsurprisingly.
I’ve found the Microsoft documentation on this a bit patchy – I haven’t found a proper reference manual. The main source seems to be a set of three articles on MSDN – search for “Customizing the 2007 Office Fluent Ribbon”.
In the next post we’ll add the Bookmarks controls, with a combo-box that we update dynamically.