Ribbon, Step-by-Step. Part 3 (The Office Menu and Re-purposing)

In our first two parts we covered the file structure of Excel 2007 and some very simple customisations which, in the main, do not require any VBA code. in this part we are going to try and accomplish two things.

1. Customisation of the Office Menu (under the office button)
2. Re-purposing built-in controls to do other things.

In the next one or two parts we will cover more details on the various controls, including Galleries and Contextual tabs and then how we can capture the ribbon object and change controls 'on the fly', so... on with Part 3

Customising the Office Menu

As we have already discovered, the access point to this menu, the Office Button, cannot currently be modified (although as we saw in Part 2, it can be removed), beneath this button is the Office Menu, which is analogous with the 'File' menu in previous versions.

In it's standard form, it looks as below. The controls with a simple image, such as 'New', are 'button' controls. Those with an arrow at the side are defined as 'splitButton', with 'menu' and 'button' controls on the fly-out menu presented from the 'splitButton' deployment.

As with all customUI code, the RibbonX starts with <customUI></customUI> tags and beneath that <ribbon></ribbon> tags. The <officeMenu></officeMenu> tags sit just below this and before any <tab></tab> members, like so: (notice we have left startFromScratch attribute off, so the code presumes "false", e.g. keep the existing ribbon):

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

The controls we require on the Office Menu, will sit between the <officeMenu></officeMenu> tags. in this example we will add one simple button, one split button with a 'sub menu' beneath, with button and checkbox and disable one of the built-in buttons.

To add a button we simply specify a new 'button' control and set a unique id, like so: (The label attribute sets the actual text that appears on the 'menu' item).

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <officeMenu>
    <button id="OfficeButton0" insertBeforeMso="FileSave" imageMso="HappyFace" label="New Button" onAction="MBox"/>
    </officeMenu>
</ribbon>
</customUI>

NOTE:You may be wondering where you get all the existing control names, such as the "FileSave" above. It is actually pretty simple. If you go to Office Button>Excel Options>Customize, you can navigate to the command you want and hover over it and the tooltip that appears in braces (FileSave), is exactly (including case) how the control should be addressed as a parameter to the attribute.. below is the tooltip. You can use the same name for any imageMso, to get the built-in icon too.

Now as we grow in confidence, we will add the two 'splitButtons', with their related 'menu' and button' controls below. both these buttons are custom ones, declared with just an id (as apposed to a built-in one that would be declared idMso). Here is the code complete:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <officeMenu>
    <button id="OfficeButton0" insertBeforeMso="FileSave" imageMso="FileSave" 
            label="New Button" onAction="MBox"/>
      <splitButton idMso="FilePrintMenu">
        <menu>
          <button id="OfficeButton1" insertBeforeMso="FilePrintPreview" imageMso="HappyFace"
            label="Happy Print" description="Prints a random joke in the footer of each document" onAction="MBox"/>
        <checkBox idMso="ViewGridlinesToggleExcel"/>
        </menu>
      </splitButton>
    <button idMso="FileClose" enabled="false"/>
    </officeMenu>
</ribbon>
</customUI>

So, reading the code top down we have

• A button (OfficeButton0), inserted before the 'Save' button with the 'Save' icon.
• A sub menu on the splitButton 'Print' menu, with a button (OfficeButton1), inserted before the print preview button, with a Happy Face built-in icon.
• The same 'sub-menu' also has a checkbox, which is the built-in one to display Excel Grid Lines. (see the idMso means a built-in control)
• The 'Close' button ("FileClose") at the base of the Office menu is disabled.

The image below show the 'New Button', the disabled 'Close' button, the HappyFace button and checkbox on the 'sub menu'

You will have noticed the first use of 'Callbacks' in the parameters above, e.g. 'onAction'. There are many of these, but let's settle on the main one at the minute which is onAction. This defines the name of the VBA code that should run when the control (button, etc) is pressed, clicked or whatever the default 'action' is.

Working with 'callbacks' could not be simpler, when using the Office 2007 Custom UI editor. You simply press the 'callback' toolbar button and it will create a new tab with all the code for all the 'action' callbacks found in the RibbonX code. Now you can simply copy this from the callback pane and paste it into either standard or class modules in your workbook and write code between the stubs to connect actions to the controls.. (when pressing the callbacks button in our example I got the following code stub).

'Callback for OfficeButton0 onAction
Sub MBox(control as IRibbonControl)
End Sub

Why only one stub when there are two 'onAction' parameters? It's because all the 'onAction' parameters are asking for the same VBA code (MBox), so we can use just one code stub. This gives us an IRibbonControl object as a variable called 'control', which we can use to interrogate which control fired the MBox code, using the id property, thus:

'Callback for OfficeButton0 onAction
Sub MBox(control As IRibbonControl)
If control.ID = "OfficeButton1" Then
    MsgBox "Why did the chicken cross the road?", vbQuestion + vbOKOnly, control.ID
Else
    MsgBox "Nicole Smith is Dead!", vbExclamation + vbOKOnly, control.ID
End If
End Sub

This would give us the relevant message box depending on the id of the 'pressed' button.

Re-Purposing Existing Controls.

As you will hopefully be starting to realise, many things you want to do with the Ribbon are starting to work out fairly simply and re-purposing existing commands is no different. You will see a clue in the text just gone. The member we will be dealing with here is the 'command', which, because Excel obviously has many 'commands' the 'command' member 'nests' within the 'commands' collection member. (Like a 'tab' is contained within the 'tabs' collection).

You identify the commands in exactly the same way as we did above, by looking for it's idMso in the customize dialog's list. Once you have this idMso, there is little left to learn. The code below disables the 'Italic' functionality on the ribbon, by setting it's 'enabled' attribute to "false" and then re-purposes the 'Save' (FileSave) menu with a custom 'OnAction' attribute. This will override the built-in save functionality in Excel. Below that is a screen shot of the disabled Italics button and the dialog 'fired' when pressing 'Save'.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="Italic" enabled="false"/>
        <command idMso="FileSave" onAction="MySave"/>
    </commands>
</customUI>

I hope, now we have covered RibbonX a few times, that little explanation is needed for this procedure, but for completeness, below is the VBA code 'callback' generated by the CustomUI tool and placed in a standard module in the workbook.

'Callback for Save onAction
Sub MySave(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "Sorry, like me, it can't be saved", vbExclamation + vbOKOnly, "Bog Off"
End Sub

As you can see, you will get an IRibbonControl object to identify the firing control and a parameter enabling you to enable or disable the default action (default is cancel=true). This enables you to only re-purpose on the pressing of a certain custom control, for example.

This will be enough for this part. next time we will extend our knowledge of other controls and then in the final part we will move on to changing control state on the fly by capturing the Ribbon object and invalidating it.

Stay tuned for the final one or two episodes! ;-)