Office 2007 Ribbon – Part 3

In the previous part we started to implement a custom tab in the Excel ribbon:

We defined an XML configuration file for the tab and the Link group, which lives inside the xlsm archive. We also wrote VBA callback procedures for the buttons’ onAction events.

For the next stage, we want to be able to create, go to and delete ‘bookmarks’ within our workbook. Bookmarks are range names (maybe temporary) which are distinct from the range names used in formulas. We’ll make them distinct using a suitable prefix – say “_BM_”. Our combo box will display only these names (minus prefix), not the full set visible in the Name Box combo.

We can define a bookmark by selecting the target range, and clicking the Make Bookmark button. An input box has a default value of the first (or only) cell; this can be changed, but the name must not be already in use. The combo box then needs to be updated with the new name (minus prefix).

Selecting from the combo box goes to the bookmark range. We need to update the combo to clear the selected value (otherwise selecting the same value again does not raise an onChange event). The Delete Bookmark button does what it says; again we need to update the combo.

First the configuration. At the same level as the Links <group> element, we need to add:

<group id="BookmarkGroup" label="Bookmarks">
  <button id="BookmarkButton"
	imageMso="FrontPageToggleBookmark"
	size="large"
	label="Make Bookmark"
	onAction="Bookmark.MakeBookmark" />
  <comboBox id="BookmarksCombo"
	label="Bookmarks"
	onChange="Bookmark.GoToBookmark"
	getItemCount="GetItemCount"
	getItemID="GetItemID"
	getItemLabel="GetItemLabel"
	getText="GetText"/>
  <button id="BookmarkDelButton"
	imageMso="FrontPageToggleBookmark"
	size="large"
	label="Delete Bookmark"
	onAction="Bookmark.DeleteBookmark" />
</group>

The buttons are as the previous examples. The combo box is rather more interesting, in that it is built by means of callbacks: GetItemCount, GetItemLabel, GetItemID, GetText. We need to provide VBA implementations for these:

Sub GetItemCount(control As IRibbonControl, ByRef count)
    count = UBound(bmarks)
End Sub

Sub GetItemLabel(control As IRibbonControl, index As Integer, _
           ByRef label)
    label = bmarks(index + 1)
End Sub

Sub GetItemID(control As IRibbonControl, index As Integer, _
           ByRef ID)
    ID = "bookmark" & index
End Sub

Sub GetText(control As IRibbonControl, ByRef text)
    text = ""   'visible value in combo when initialized
End Sub

The Item Labels are the visible items in the combo box drop-down. In our case, these come from an array of the Bookmark names bmarks, which is held as a module variable. The bmarks variable is set by a procedure which goes through the ActiveWorkbook Names, extracting the Bookmark ones.

The Label and ID procedures take an index parameter, which implies that they are called iteratively by the ribbon. Notice that these are subs (rather than functions), which pass back their result via a ByRef parameter.

Now we need to make the combo list update whenever an action has been performed. We do this by making a call to the ribbon (I suppose this is a ‘callforward’):

Sub ResetBookmarks()
    Bookmarks   'reset bmarks array
    ribbon.InvalidateControl ("BookmarksCombo")
End Sub

The argument is the control Id, as in the configuration. We can also invalidate the whole ribbon, should multiple controls need re-initializing.

How do we get the ribbon reference? Via a callback when the ribbon loads initially. The callback is specified on the root node of the configuration:

<customUI
  xmlns="http://schemas.microsoft.com/office/2006/01/customui"
  onLoad="RibbonLoad">

The RibbonLoad procedure gets a ribbon reference, assigns it to a module variable, and also sets the Bookmarks array:

Sub RibbonLoad(rb As IRibbonUI)
    Set ribbon = rb
    Bookmarks
End Sub

That’s about it. As I said earlier, the documentation – for example of the callbacks – isn’t great, so a certain amount of experimentation is needed.

Here’s the XML configuration file for the ribbon extensions; here’s the Definition module; and here’s the Bookmark module.

Advertisements

3 Responses to “Office 2007 Ribbon – Part 3”


  1. 1 John Doe May 9, 2014 at 7:22 pm

    Looks like the links for the xml config, def and bookmark files isn’t working. http://roymaclean-it.co.uk/ doesn’t seem to be a valid site anymore.

  2. 2 Roberto July 28, 2014 at 8:18 pm

    Hi! nice post!
    Download links are broken. Please fix them.

  3. 3 Stephen August 5, 2016 at 6:55 am

    Thanks for post; links did not go to download


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




December 2009
M T W T F S S
« Nov   Jan »
 123456
78910111213
14151617181920
21222324252627
28293031  

%d bloggers like this: