Chapter 22: Working with the Ribbon

IN THIS CHAPTER

• Looking at the Excel Ribbon UI from a user's perspective

• Using VBA to work with the Ribbon

• Customizing the Ribbon with RibbonX code

• Looking at examples of workbooks that modify the Ribbon

• Using boiler-plate code for creating an old-style toolbar

Ribbon Basics

Beginning with Microsoft Office 2007, the time-honored menu-and-toolbar user interface was scrapped and replaced with a new tab-and-Ribbon interface. Although the new interface kind of resembles the old-fashioned menus-and-toolbars interface, you'll find that it's radically different.

Long-time Excel users probably noticed that the menu system had become increasingly complicated with each new version. In addition, the number of toolbars had become almost overwhelming. After all, every new feature must be accessible. In the past, this access meant adding more items to the menus and building new toolbars. The Microsoft designers set out to solve this overcrowding problem, and the Ribbon interface was their solution.

Reactions to the Office Ribbon interface can best be described as mixed. As with anything new, some people love it, and others hate it. Count me among the former group. After using Excel 2007 for more than three years, it's painful for me to go back to the confusing menu system in Excel 2003.

Many experienced Excel users suffered from a mild case of bewilderment when they realized that all their familiar command sequences no longer worked. Beginning users, on the other hand, are usually able to get up to speed much more quickly because they aren't overwhelmed with irrelevant menus and toolbars.

For the benefit of Ribbon newcomers, I provide some additional user-oriented information in the sections that follow.

The commands available in the Ribbon vary, depending on which tab is selected. The Ribbon is arranged into groups of related commands. Here's a quick overview of Excel's tabs:

• Home: You'll probably spend most of your time in the Home tab. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows and columns, plus an assortment of worksheet-editing commands.

• Insert: Select this tab when you need to insert something in a worksheet — a table, a diagram, a chart, a symbol, and so on.

• Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that deal with printing.

• Formulas: Use this tab to insert a formula, name a range, access the formula-auditing tools, or control how Excel performs calculations.

• Data: Excel's data-related commands are on this tab.

• Review: This tab contains tools to check spelling, translate words, add comments, and protect sheets.

• View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available on the status bar.

• Developer: This tab isn't visible by default. It contains commands that are useful for programmers. To display the Developer tab, right-click the Ribbon and choose Customize The Ribbon. In the Customize Ribbon tab of the Excel Options dialog box, place a check mark next to Developer.

• Add-Ins: This tab is visible only if you've loaded a workbook or add-in that customizes the menu or toolbars (by using the CommandBars object). Because menus and toolbars are no longer available, these customizations appear in the Add-Ins tab.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display everything, the commands adapt and may seem to be missing, but the commands are still available. Figure 22-1 shows three views of the Home tab of the Ribbon. In the top image, all controls are fully visible. In the middle image, Excel's window is made narrower. Notice that some of the descriptive text is gone, and some of the icons are smaller. The bottom image shows the extreme case in which the window is very narrow. Some groups display a single icon. However, if you click the icon, all the group commands are available to you.

If you'd like to hide the Ribbon to increase your worksheet view, just double-click any of the tabs. The Ribbon goes away (but the tabs remain), and you're able to see about five additional rows of your worksheet. When you need to use the Ribbon again, just click a tab, and it comes back temporarily. To permanently restore the Ribbon, double-click a tab. You can also press Ctrl+F1 to toggle the Ribbon display on and off, or use the ^ icon (next to the Help icon in the Excel title bar).

FIGURE 22-1: The Home tab of the Ribbon, with varying widths of the Excel window.

Using VBA with the Ribbon

Now, the big question: What can a VBA programmer do with the Ribbon? The simple answer is this: not much.

Following is a list of what you can do with the Ribbon using VBA:

• Determine whether a particular control is enabled.

• Determine whether a particular control is visible.

• Determine whether a particular control is pressed (for toggle buttons and check boxes).

• Get a control's label, screen tip, or supertip (a more detailed description of the control).

• Display the image associated with a control.

• Execute the command associated with a particular control.

Following is a list of things that you might like to do with the Ribbon but that aren't possible:

• Determine which tab is currently selected.

• Activate a particular tab.

• Add a new tab.

• Add a new group to a tab.

• Add a new control.

• Remove a control.

• Disable a control.

• Hide a control.

In Excel 2010, the user can make modifications to the Ribbon by using the Customize Ribbon tab of the Excel Options dialog box. Unfortunately, you can't use VBA to make these changes.

Accessing a Ribbon control

All told, Excel has more than 1,700 Ribbon controls. Every Ribbon control has a name, and you use that name when you work with the control using VBA.

For example, the statement that follows displays a message box that shows the Enabled status of the ViewCustomViews control. (This control is located in the View⇒Workbook Views group.)

MsgBox Application.CommandBars.GetEnabledMso(“ViewCustomViews”)

Normally, this control is enabled. But (inexplicably), if the workbook contains a table (created by Insert⇒Tables⇒Table), the ViewCustomViews control is disabled.

Determining the name of a particular control is a manual task. First, display the Customize Ribbon tab of the Excel Options dialog box. Locate the control in the list box on the left and then hover the mouse pointer over the item. The control's name appears in a pop-up screen tip, in parentheses (see Figure 22-2).

FIGURE 22-2: Using the Customize Ribbon tab of the Excel Options dialog box to determine the name of a control.

Unfortunately, it's not possible to write VBA code to loop through all the controls on the Ribbon and display a list of their names.

The companion CD-ROM contains a workbook with the names of all Excel controls. The workbook also displays additional information about each control, including the control type, the tab name, and the group name. Figure 22-3 shows a portion of this file, which is named ribbon control names.xlsx.

FIGURE 22-3: A workbook that displays information about each Ribbon control.

Working with the Ribbon

In the previous section I provided an example of using the GetEnabledMso method of the CommandBars object. Following is a list of all the methods that are relevant to working with the Ribbon via the CommandBars object. All these methods take one argument: idMso, which represents the name of the command.

• ExecuteMso: Executes a control

• GetEnabledMso: Returns True if the specified control is enabled

• GetImageMso: Returns the image for a control

• GetLabelMso: Returns the label for a control

• GetPressedMso: Returns True if the specified control is pressed (applies to check box and toggle button controls)

• GetScreentipMso: Returns the screen tip for a control (the text that appears in the control)

• GetSupertipMso: Returns the supertip for a control (the description of the control that appears when you hover the mouse pointer over the control)

Some of these methods are fairly useless. Why would a VBA programmer need to determine the screen tip for a control? I can't think of a reason.

The VBA statement that follows toggles the Selection pane (a feature introduced in Excel 2007 that facilitates selecting objects on a worksheet):

Application.CommandBars.ExecuteMso(“SelectionPane”)

The following statement displays the Paste Special dialog box (and will display an error message if the Windows Clipboard is empty):

Application.CommandBars.ExecuteMso(“PasteSpecialDialog”)

Here's a command that tells you whether the formula bar is visible (it corresponds to the state of the Formula Bar control in the View⇒Show group):

MsgBox Application.CommandBars.GetPressedMso(“ViewFormulaBar”)

Note, however, that your code can't change the visibility of the formula bar by accessing the Ribbon control. Rather, use the DisplayFormulaBar property of the Application object:

Application.DisplayFormulaBar = True

The statement that follows displays True if the Merge & Center control is enabled. (This control is disabled if the sheet is protected or if the active cell is within a table.)

MsgBox Application.CommandBars.GetEnabledMso(“MergeCenter”)

The following VBA code adds an ActiveX Image control to the active worksheet and uses the GetImageMso method to display the “binoculars” icon from the Find & Select control in the Home⇒Editing group:

Sub ImageOnSheet()

Dim MyImage As OLEObject

Set MyImage = ActiveSheet.OLEObjects.Add _

(ClassType:=”Forms.Image.1”, _

Left:=50, _

Top:=50)

With MyImage.Object

.AutoSize = True

.BorderStyle = 0

.Picture = Application.CommandBars. _

GetImageMso(“FindDialog”, 32, 32)

End With

End Sub

To display the Ribbon icon in an Image control (named Image1) on a UserForm, use this procedure:

Private Sub UserForm_Initialize()

With Image1

.Picture = Application.CommandBars. _

GetImageMso(“FindDialog”, 32, 32)

.AutoSize = True

End With

End Sub

Activating a tab

Microsoft provides no direct way to activate a Ribbon tab from VBA. But if you really need to do so, using SendKeys is your only option. The SendKeys method simulates keystrokes. The keystrokes required to activate the Home tab are Alt, followed by H. These keystrokes display the keytips in the Ribbon. To hide the keytips, press F6. Using this information, the following statement sends the keystrokes required to activate the Home tab:

Application.SendKeys “%h{F6}”

The SendKeys arguments for the other tabs are

• Insert: “%n{F6}”

• Page Layout: “%p{F6}”

• Formulas: “%m{F6}”

• Data: “%a{F6}”

• Review: “%r{F6}”

• View: “%w{F6}”

• Developer: “%l{F6}”

• Add-Ins: “%x{F6}”

Using SendKeys may not be perfectly reliable. For example, if you execute the previous example while a UserForm is displayed, the keystrokes will be sent to the UserForm, not to the Ribbon.

Customizing the Ribbon

You can't perform any Ribbon modifications using VBA. Rather, you must write RibbonX code and insert the code into the workbook file — which is done outside of Excel. You can, however, create VBA callback procedures. A callback procedure is a VBA macro that is executed when a custom Ribbon control is activated.

RibbonX code is XML markup that describes the controls, where in the Ribbon they're displayed, what they look like, and what happens when they're activated. This book does not cover RibbonX — it's complex enough to be the subject of an entire book. I do, however, provide a few simple examples so that you can understand what's involved in modifying the Excel UI and decide whether it's something you'd like to learn.

For information about Excel's file structure, refer to Chapter 4. That section describes how to view the information inside of an XLSX workbook file.

A simple RibbonX example

This section contains a step-by-step walkthrough that will give you a feel for what it takes to modify Excel's Ribbon. This example creates a new Ribbon group (named Custom) on the Data tab. It also creates two buttons in the new Ribbon group, labeled Hello World and Goodbye World. Clicking either of these buttons executes a VBA macro.

The instructions that follow are tedious and error-prone. In reality, most developers don't use this method. Rather, they use software designed to make the process much easier.

Follow these steps to create a workbook that contains RibbonX code that modifies the Ribbon:

1. Create a new Excel workbook, insert a VBA module, and enter the two callback procedures that follow.

These procedures are the ones that execute when the buttons are clicked:

Sub HelloWorld(control As IRibbonControl)

MsgBox “Hello World!”

End Sub

Sub GoodbyeWorld(control As IRibbonControl)

ThisWorkbook.Close

End Sub

2. Save the workbook and name it ribbon modification.xlsm.

3. Close the workbook.

4. Locate the folder that contains the ribbon modification.xlsm file and create a folder named customUI.

5. Inside the customUI folder, use a text editor (such as Windows Notepad) to create a text file named customUI.xml with the following RibbonX XML code:

If your system is set up to hide extensions of known file types, you should turn off that option so that you always see file extensions. In Windows Explorer, use Tools⇒Folder Options, and select the View tab in the Folder Options dialog box. Remove the check mark from Hide Extensions For Known File Types.

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

<ribbon>

<tabs>

<tab idMso=”TabData”>

<group id=”Group1” label=”Custom”>

<button id=”Button1”

label=”Hello World”

size=”normal”

onAction=”HelloWorld”

imageMso=”HappyFace” />

<button id=”Button2”

label=”Goodbye World”

size=”normal”

onAction=”GoodbyeWorld”

imageMso=”DeclineInvitation” />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

6. Using Windows Explorer, add a .zip extension to the ribbon modification.xlsm file in Windows Explorer.

The filename should now be ribbon modification.xlsm.zip.

7. Drag the customUI folder you created in Step 4 into the ribbon modification.xlsm.zip file.

Windows treats Zip files as if they were folders, so drag-and-drop operations are allowed.

8. Double-click the ribbon modification.xlsm.zip file to open it.

Figure 22-4 shows the contents of the Zip file. As you see, the file contains several folders.

FIGURE 22-4: An Excel workbook, displayed as a Zip file.

9. Double-click the _rels folder within the Zip file.

This folder contains one file, named .rels.

10. Drag the .rels file to a location outside the Zip file (to your desktop, for example).

11. Open the .rels file (which is an XML file) with a text editor, such as Notepad.

12. Add the following line to the .rels file, before the </Relationships> tag:

<Relationship Type=”http://schemas.microsoft.com/office/2006/relationships/ui/extensibility” Target=”/customUI/customUI.xml” Id=”12345” />

13. Save the .rels file and drag it back into the Zip file, overwriting the original version.

14. Remove the .zip extension so that the file is back to its original name: ribbon modification.xlsm.

Open the workbook in Excel. If all went well, you should see a new group with two buttons in the Data tab (see Figure 22-5).

FIGURE 22-5: RibbonX code created a new group with two buttons.

This workbook, named ribbon modification.xlsm, is available on the companion CD-ROM.

It's important to understand that the Ribbon modification is document-specific. In other words, the new Ribbon group is displayed only when the workbook that contains the RibbonX code is the active workbook. This is a major departure from how UI modifications worked in versions prior to Excel 2007.

To display Ribbon customizations when any workbook is active, convert the workbook to an add-in file or add the RibbonX code to your Personal Macro Workbook.

If you've concluded that modifying Excel's Ribbon isn't worth the effort, don't despair. Tools are available that make the process much less tedious than I've described.

A simple Ribbon example, take 2

This section provides step-by-step instructions for making the same Ribbon modification described in the previous section. In this example, I use the Custom UI Editor for Microsoft Office. This program still requires that you create the RibbonX code manually, but it will validate the code for you. It also eliminates all the tedious manual file manipulations. And finally, it can generate the VBA callback procedure declarations, which you can copy and paste to your VBA module.

You can download a free copy of the Custom UI Editor for Microsoft Office from

http://openxmldeveloper.org/articles/customuieditor.aspx

To add the new group and buttons (as described in the previous section) using the Custom UI Editor:

1. In Excel, create a new workbook and save it as a macro-enabled XLSM file.

2. Close the workbook.

3. Launch the Custom UI Editor For Microsoft Office.

4. Choose File⇒Open and locate the workbook you saved in Step 1.

5. Choose Insert⇒Office 2007 Custom UI Part.

Choosing this option will make the file compatible with both Excel 2007 and Excel 2010.

6. Enter the RibbonX code shown in Figure 22-6.

FIGURE 22-6: The Custom UI Editor for Microsoft Office.

7. Click the validate button to check for errors.

8. Click the Generate Callbacks button and copy the code that appears.

The Custom UI Editor generates two VBA callback procedures (see Figure 22-7). Select and copy this code; you will later paste it into a VBA module the workbook.

9. Click the customUI.xml node in the tree diagram on the left.

10. Choose File⇒Save and then File⇒Close.

FIGURE 22-7: The Custom UI Editor generated two VBA callback procedures.

11. Activate Excel and open the workbook.

12. Press Alt+11 to activate the VB Editor.

13. Insert a VBA module and paste the code you copied in Step 8.

14. Add a MsgBox statement to each of the two procedures so that you can verify that they're being executed.

As you can see, working with the Custom UI Editor is much easier than manipulating a file manually.

Figure 22-6 shows the Custom UI Editor.

The CUSTOM UI Part

In Step 5 of the preceding instructions, you inserted a customUI part for Office 2007. This choice makes the workbook compatible with Excel 2007 and Excel 2010. The other option on the Insert menu is Office 2010 Custom UI Part. If you put the RibbonX code in and Office 2010 Custom UI Part, the workbook won't be compatible with Excel 2007.

If your application doesn't use any commands that are unique to Excel 2010, using the Office 2007 custom UI part is the best solution. Also, keep in mind that a single file can have both an Office 2007 part and an Office 2010 part. You use two parts if you want to load version-specific RibbonX code for the UI. For example, you might write code to a command to the Excel 2010 Backstage View. Excel 2007 doesn't have Backstage View, so you'd write different code to add a command to the Excel 2007 Office button.

Note that the first statement in the RibbonX code must be changed for an Office 2010 Custom UI Part. The code must refer to this namespace:

<customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui'>

If you use the wrong customUI tag, the Custom UI Editor will let you know when you validate the code.

VBA callback procedures

Recall that the workbook contains two VBA procedures, HelloWorld and GoodbyeWorld. These procedure names correspond to the onAction parameters in the RibbonX code. The onAction parameter is one way to link the RibbonX code to your VBA code.

Both the VBA procedures contain an argument named control, which is an IRibbonControl object. This object has three properties, which you can access in your VBA code:

• Context: A handle to the active window containing the Ribbon that triggered the callback. For example, use the following expression to get the name of the workbook that contains the RibbonX code:

control.Context.Caption

• Id: Contains the name of the control, specified as its Id parameter.

• Tag: Contains any arbitrary text that's associated with the control.

The VBA callback procedures can be as complex as necessary.

The .rels file

Inserting the file that contains the RibbonX code has no effect unless you specify a relationship between the document file and the customization file. These relationships, written in XML, are stored in the .rels file, which is in the _rels folder. Here's the relationship for the example presented in the previous section:

<Relationship Type=”http://schemas.microsoft.com/office/2006/

relationships/ui/extensibility” Target=”/customUI/customUI.xml”

Id=”12345” />

The Target parameter points to the customUI.xml file that contains the RibbonX code. The Id parameter contains an arbitrary text string. The string can contain anything, as long as it's unique to the file (that is, as long as no other <Relationship> tag uses the same Id).

If you use the Custom UI Editor, you need not be concerned with the .rels file. Changes to this file are made automatically.

The RibbonX code

And now, the tricky part. Writing the XML code that defines your UI modification is no easy task. As I've noted, this is not the book that will teach you how to write RibbonX code. You'll find a few simple examples here, but you'll need to consult other sources for the fine points.

When you're starting out, it's best to start with examples that work (search the Web) and then make small modifications, testing frequently along the way. It can be very frustrating to spend an hour working on code that appears to be perfect in every way — and then realize that XML is case-sensitive. ID is not the same as Id.

You may be curious about the imageMso parameter, which determines which icon is displayed next to the control. Microsoft Office includes more than 1,000 icons that you can use with Ribbon controls. Each is accessed by its name. For more information, see the sidebar “Using imageMso images.”

Another RibbonX example

This section contains another example of using RibbonX to modify the UI. This workbook creates a new group on the Page Layout tab and adds a check box control that toggles the display of page breaks.

Although Excel has more than 1,700 commands, it doesn't have a command that toggles the page break display. After printing or previewing a worksheet, the only way to hide the page break display is to use the Excel Options dialog box. Therefore, this example also has some practical value.

This example is a bit tricky because it requires that the new Ribbon control be in synch with the active sheet. For example, if you activate a worksheet that doesn't display page breaks, the check box control should be in its unchecked state. If you activate a worksheet that displays page breaks, the control should be checked. Furthermore, page breaks aren't relevant for a chart sheet, so the control should be disabled if you activate a chart sheet.

The RibbonX Code

The RibbonX code that adds a new group (with a CheckBox control) to the Page Layout tab follows:

<customUI

xmlns=”http://schemas.microsoft.com/office/2006/01/customui”

onLoad=”Initialize”>

<ribbon>

<tabs>

<tab idMso=”TabPageLayoutExcel”>

<group id=”Group1” label=”Custom”>

<checkBox id=”Checkbox1”

label=”Page Breaks”

onAction=”TogglePageBreakDisplay”

getPressed=”GetPressed”

getEnabled=”GetEnabled”/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

This RibbonX code references four VBA callback procedures (each of which is described later):

• Initialize: Executed when the workbook is opened.

• TogglePageBreakDisplay: Executed when the user clicks the check box control.

• GetPressed: Executed when the control is invalidated (the user activates a different sheet).

• GetEnabled: Executed when the control is invalidated (the user activates a different sheet).

Figure 22-8 shows the new control.

FIGURE 22-8: This check box control is always in synch with the page break display of the active sheet.

The VBA Code

The CustomUI tag includes an onLoad parameter, which specifies the Initialize VBA callback procedure, as follows (this code is in a standard VBA module):

Public MyRibbon As IRibbonUI

Sub Initialize(Ribbon As IRibbonUI)

‘ Executed when the workbook loads

Set MyRibbon = Ribbon

End Sub

The Initialize procedure creates an IRibbonUI object named MyRibbon. Notice that MyRibbon is a Public variable, so it's accessible from other procedures in the module.

I created a simple event procedure that is executed whenever a worksheet is activated. This procedure, which is located in the ThisWorkbook code module, calls the CheckPageBreakDisplay procedure:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Call CheckPageBreakDisplay

End Sub

The CheckPageBreakDisplay procedure invalidates the check box control. In other words, it destroys any data associated with that control.

Sub CheckPageBreakDisplay()

‘ Executed when a sheet is activated

MyRibbon.InvalidateControl (“Checkbox1”)

End Sub

When a control is invalidated, the GetPressed and GetEnabled procedures are called.

Sub GetPressed(control As IRibbonControl, ByRef returnedVal)

‘ Executed when the control is invalidated

On Error Resume Next

returnedVal = ActiveSheet.DisplayPageBreaks

End Sub

Sub GetEnabled(control As IRibbonControl, ByRef returnedVal)

‘ Executed when the control is invalidated

returnedVal = TypeName(ActiveSheet) = “Worksheet”

End Sub

Notice that the returnedVal argument is passed ByRef. This means that your code is able to change the value. And that's exactly what happens. In the GetPressed procedure, the returnedVal variable is set to the status of the DisplayPageBreaks property of the active sheet. The result is that the control's Pressed parameter is True if page breaks are displayed (and the control is checked). Otherwise, the control isn't checked.

In the GetEnabled procedure, the returnedVal variable is set to True if the active sheet is a worksheet (as opposed to a chart sheet). Therefore, the control is enabled only when the active sheet is a worksheet.

The only other VBA procedure is the onAction procedure, TogglePageBreakDisplay, which is executed when the user checks or unchecks the check box:

Sub TogglePageBreakDisplay(control As IRibbonControl, pressed As Boolean)

‘ Executed when check box is clicked

On Error Resume Next

ActiveSheet.DisplayPageBreaks = pressed

End Sub

This pressed argument is True if the user checks the check box and False if he unchecks the check box. The code sets the DisplayPageBreaks property accordingly.

This workbook, named page break display.xlsm, is available on the companion CD-ROM. The CD also contains an add-in version of this workbook (named page break display add-in.xlam), which makes the new UI command available for all workbooks. The add-in version uses a class module to monitor sheet activation events for all workbooks. Refer to Chapter 19 for more information about events, and Chapter 29 for more information about class modules.

Ribbon controls demo

Figure 22-9 shows a custom Ribbon tab (My Stuff) with four groups of controls. In this section, I briefly describe the RibbonX code and the VBA callback procedures.

FIGURE 22-9: A new Ribbon tab with four groups of controls.

This workbook, named ribbon controls demo.xlsm, is available on the companion CD-ROM.

Creating a new tab

The RibbonX code that creates the new tab is

<ribbon>

<tabs>

<tab id=”CustomTab” label=”My Stuff”>

</tab>

</tabs>

</ribbon>

If you'd like to create a minimal UI, the ribbon tag has a startFromScratch attribute. If set to True, all the built-in tabs are hidden.

<ribbon startFromScratch=”true” >

Creating a Ribbon group

The code in the ribbon controls demo.xlsm example creates four groups on the My Stuff tab. Here's the code that creates the four groups:

<group id=”Group1” label=”Stuff”>

</group>

<group id=”Group2” label=”More Stuff”>

</group>

<group id=”Group3” label=”Built In Stuff”>

</group>

<group id=”Group4” label=”Galleries”>

</group>

Theses pairs of <group> and </group> tags are located within the <tab> and </tab> tags that create the new tab.

Creating controls

Following is the RibbonX code that creates the controls in the first group (Stuff), shown in Figure 22-10. Notice that the controls are defined within the first set of <group> </group> tags.

FIGURE 22-10: A Ribbon group with four controls.

<group id=”Group1” label=”Stuff”>

<labelControl id=”Label1” getLabel=”getLabel1” />

<labelControl id=”Label2” getLabel=”getLabel2” />

<editBox id=”EditBox1”

showLabel=”true”

label=”Number:”

onChange=”EditBox1_Change”/>

<button id=”Button1”

label=”Calculator”

size=”large”

onAction=”ShowCalculator”

imageMso=”Calculator” />

</group>

Two label controls each have an associated VBA callback procedure (named getLabel1 and getLabel2). These procedures are:

Sub getLabel1(control As IRibbonControl, ByRef returnedVal)

returnedVal = “Hello “ & Application.UserName

End Sub

Sub getLabel2(control As IRibbonControl, ByRef returnedVal)

returnedVal = “Today is “ & Date

End Sub

When the RibbonX code is loaded, these two procedures are executed, and the captions of the label controls are dynamically updated with the username and the date.

The editBox control has an onChange callback procedure named EditBox1_Change, which displays the square root of the number entered (or an error message if the square root can't be calculated). The EditBox1_Change procedure is

Sub EditBox1_Change(control As IRibbonControl, text As String)

Dim squareRoot As Double

On Error Resume Next

squareRoot = Sqr(text)

If Err.Number = 0 Then

MsgBox “The square root of “ & text & “ is: “ & squareRoot

Else

MsgBox “Enter a positive number.”, vbCritical

End If

End Sub

The last control in the Stuff group is a simple button. Its onAction parameter executes a VBA procedure named ShowCalculator — which uses the VBA Shell function to display the Windows calculator:

Sub ShowCalculator(control As IRibbonControl)

On Error Resume Next

Shell “calc.exe”, vbNormalFocus

If Err.Number <> 0 Then MsgBox “Can't start calc.exe”

End Sub

Figure 22-11 shows the controls in the second group, labeled More Stuff.

FIGURE 22-11: Three controls in a custom Ribbon group.

The RibbonX code for the second group is as follows:

<group id=”Group2” label=”More Stuff”>

<toggleButton id=”ToggleButton1”

size=”large”

imageMso=”FileManageMenu”

label=”Toggle Me”

onAction=”ToggleButton1_Click” />

<separator id=”sep1” />

<checkBox id=”Checkbox1”

label=”Checkbox”

onAction=”Checkbox1_Change”/>

<comboBox id=”Combo1”

label=”Month”

onChange=”Combo1_Change”>

<item id=”Month1” label=”January” />

<item id=”Month2” label=”February”/>

<item id=”Month3” label=”March”/>

<item id=”Month4” label=”April”/>

<item id=”Month5” label=”May”/>

<item id=”Month6” label=”June”/>

<item id=”Month7” label=”July”/>

<item id=”Month8” label=”August”/>

<item id=”Month9” label=”September”/>

<item id=”Month10” label=”October”/>

<item id=”Month11” label=”November”/>

<item id=”Month12” label=”December”/>

</comboBox>

</group>

The group contains a toggleButton, a separator, a checkBox, and a comboBox control. These controls are fairly straightforward. Except for the separator control (which inserts a vertical line), each has an associated callback procedure that simply displays the status of the control:

Sub ToggleButton1_Click(control As IRibbonControl, ByRef returnedVal)

MsgBox “Toggle value: “ & returnedVal

End Sub

Sub Checkbox1_Change(control As IRibbonControl, pressed As Boolean)

MsgBox “Checkbox value: “ & pressed

End Sub

Sub Combo1_Change(control As IRibbonControl, text As String)

MsgBox text

End Sub

The comboBox control also accepts user-entered text. If you would like to limit the choices to those that you provide, use a dropDown control.

The controls in the third group consist of built-in controls (see Figure 22-12). To include a built-in control in a custom group, you just need to know its name (the idMso parameter).

FIGURE 22-12: This group contains built-in controls.

The RibbonX code is

<group id=”Group3” label=”Built In Stuff”>

<control idMso=”Copy” label=”Copy” />

<control idMso=”Paste” label=”Paste” enabled=”true” />

<control idMso=”WindowSwitchWindowsMenuExcel”

label=”Switch Window” />

<control idMso=”Italic” />

<control idMso=”Bold” />

<control idMso=”FileOpen” />

</group>

These controls don't have callback procedures because they perform the standard action.

Figure 22-13 shows the final group of controls, which consists of two galleries.

FIGURE 22-13: This Ribbon group contains two galleries.

The RibbonX code for these two gallery controls is

<group id=”Group4” label=”Galleries”>

<gallery id=”Gallery1”

imageMso=”ViewAppointmentInCalendar”

label=”Pick a Month:”

columns=”2” rows=”6”

onAction=”MonthSelected” >

<item id=”January” label=”January” imageMso=”QuerySelectQueryType”/>

<item id=”February” label=”February” imageMso=”QuerySelectQueryType”/>

<item id=”March” label=”March” imageMso=”QuerySelectQueryType”/>

<item id=”April” label=”April” imageMso=”QuerySelectQueryType”/>

<item id=”May” label=”May” imageMso=”QuerySelectQueryType”/>

<item id=”June” label=”June” imageMso=”QuerySelectQueryType”/>

<item id=”July” label=”July” imageMso=”QuerySelectQueryType”/>

<item id=”August” label=”August” imageMso=”QuerySelectQueryType”/>

<item id=”September” label=”September” imageMso=”QuerySelectQueryType”/>

<item id=”October” label=”October” imageMso=”QuerySelectQueryType”/>

<item id=”November” label=”November” imageMso=”QuerySelectQueryType”/>

<item id=”December” label=”December” imageMso=”QuerySelectQueryType”/>

<button id=”Today”

label=”Today...”

imageMso=”ViewAppointmentInCalendar”

onAction=”ShowToday”/>

</gallery>

<gallery id=”Gallery2”

label=”Banjo Players”

size=”large”

columns=”4”

itemWidth=”100” itemHeight=”125”

imageMso= “Camera”

onAction=”OnAction”>

<item id=”bp01” image=”bp01” />

<item id=”bp02” image=”bp02” />

<item id=”bp03” image=”bp03” />

<item id=”bp04” image=”bp04” />

<item id=”bp05” image=”bp05” />

<item id=”bp06” image=”bp06” />

<item id=”bp07” image=”bp07” />

<item id=”bp08” image=”bp08” />

<item id=”bp09” image=”bp09” />

<item id=”bp10” image=”bp10” />

<item id=”bp11” image=”bp11” />

<item id=”bp12” image=”bp12” />

<item id=”bp13” image=”bp13” />

<item id=”bp14” image=”bp14” />

<item id=”bp15” image=”bp15” />

</gallery>

</group>

Figure 22-14 shows the first gallery, a list of month names in two columns. The onAction parameter executes the MonthSelected callback procedure, which displays the selected month (which is stored as the id parameter):

Sub MonthSelected(control As IRibbonControl, _

id As String, index As Integer)

MsgBox “You selected “ & id

End Sub

The Pick a Month gallery also contains a button control with its own callback procedure (labeled Today) at the bottom:

Sub ShowToday(control As IRibbonControl)

MsgBox “Today is “ & Date

End Sub

FIGURE 22-14: A gallery that displays month names, plus a button.

The second gallery, shown in Figure 22-15, displays 15 photos.

These photos are stored in the workbook file, in a folder named images, within the customUI folder. Adding images also requires a _rels folder, with a list of relationships. To see how this works, add a .zip extension to the workbook and then examine its contents.

FIGURE 22-15: A gallery of photos.

A DynamicMenu Control Example

One of the most interesting Ribbon controls is the dynamicMenu control. This control lets your VBA code feed XML data into the control — which provides the basis for menus that change based on context.

Setting up a dynamicMenu control isn't a simple task, but this control probably offers the most flexibility in terms of using VBA to modify the Ribbon dynamically.

I created a simple dynamicMenu control demo that displays a different menu for each of the three worksheets in a workbook. Figure 22-16 shows the menu that appears when Sheet1 is active. When a sheet is activated, a VBA procedure sends XML code specific for the sheet. For this demo, I stored the XML code directly in the worksheets to make it easier to read. Alternatively, the XML markup can be stored as a string variable in your code.

FIGURE 22-16: The dynamicMenu control lets you create a menu that varies depending on the context.

The RibbonX code that creates the new tab, the new group, and the dynamicMenu control follows:

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

<ribbon>

<tabs>

<tab id=”CustomTab” label=”Dynamic”>

<group id=”group1” label=”Dynamic Menu Demo”>

<dynamicMenu id=”DynamicMenu”

getContent=”dynamicMenuContent”

imageMso=”RegionLayoutMenu”

size = “large”

label=”Sheet-Specific Menu”/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

This example needs a way to invalidate the Ribbon whenever the user activates a new sheet. I use the same method I used for the page break display example earlier in this chapter (see “Another RibbonX example”): I declared a Public variable, MyRibbon, of type IRibbonUI. I used a Workbook_SheetActivate procedure that called the UpdateDynamicRibbon procedure whenever a new sheet is activated:

Sub UpdateDynamicRibbon()

‘ Invalidate the Ribbon to force a call to dynamicMenuContent

On Error Resume Next

MyRibbon.Invalidate

If Err.Number <> 0 Then

MsgBox “Lost the Ribbon object. Save and reload.”

End If

End Sub

The UpdateDynamicRibbon procedure invalidates the MyRibbon object, which forces a call to the VBA callback procedure named dynamicMenuContent (a procedure referenced by the getContent parameter in the RibbonX code). Notice the error-handling code. Some edits to your VBA code destroy the MyRibbon object, which is created when the workbook is opened. Attempting to invalidate an object that doesn't exist causes an error, and the message box informs the user that the workbook must be saved and reopened. Unfortunately, reopening the workbook is the only way to re-create the MyRibbon object.

The dynamicMenuContent procedure follows. This procedure loops through the cells in column A of the active sheet, reads the XML code, and stores it in a variable named XMLcode. When all the XML has been appended, it's passed to the returnedVal argument. The net effect is that the dynamicMenu control has new code, so it displays a different set of menu options.

Sub dynamicMenuContent(control As IRibbonControl, _

ByRef returnedVal)

Dim r As Long

Dim XMLcode As String

‘ Read the XML markup from the active sheet

For r = 1 To Application.CountA(Range(“A:A”))

XMLcode = XMLcode & ActiveSheet.Cells(r, 1) & “ “

Next r

returnedVal = XMLcode

End Sub

The workbook that contains this example is available on the companion CD-ROM. The filename is dynamicmenu.xlsm.

More on Ribbon customization

I conclude this section with some additional points to keep in mind as you explore the wonderful world of Excel Ribbon customization:

• When you're working with the Ribbon, make sure that you turn on error message display. Refer to the “See your errors” sidebar, earlier in this chapter.

• Remember that RibbonX code is case-sensitive.

• All the named control IDs are in English, and they're the same across all language versions of Excel. Therefore, Ribbon modifications work regardless of what language version of Excel is used.

• Ribbon modifications appear only when the workbook that contains the RibbonX code is active. To make Ribbon modifications appear for all workbooks, the RibbonX code must be in an add-in.

• The built-in controls scale themselves when the Excel window is resized. In Excel 2007, custom controls do not scale, but they do in Excel 2010.

• Adding or removing controls from a built-in Ribbon group is not possible.

• You can, however, hide tabs. The RibbonX code that follows hides three tabs:

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

<ribbon>

<tabs>

<tab idMso=”TabPageLayoutExcel” visible=”false” />

<tab idMso=”TabData” visible=”false” />

<tab idMso=”TabReview” visible=”false” />

</tabs>

</ribbon>

</customUI>

• You can also hide groups within a tab. Here's RibbonX code that hides four groups on the Insert tab (leaving only the Charts group):

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

<ribbon>

<tabs>

<tab idMso=”TabInsert”>

<group idMso=”GroupInsertTablesExcel” visible=”false” />

<group idMso=”GroupInsertIllustrations” visible=”false” />

<group idMso=”GroupInsertLinks” visible=”false” />

<group idMso=”GroupInsertText” visible=”false” />

</tab>

</tabs>

</ribbon>

</customUI>

• You can assign your own macro to a built-in control. This is known as repurposing the control. The RibbonX code that follows intercepts three built-in commands:

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

<commands>

<command idMso=”FileSave” onAction=”mySave”/>

<command idMso=”FilePrint” onAction=”myPrint”/>

<command idMso=”FilePrintQuick” onAction=”myPrint”/>

</commands>

</customUI>

• You can also write RibbonX code to disable one or more built-in controls. The code that follows disables the Insert ClipArt command:

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

<commands>

<command idMso=”ClipArtInsert” enabled=”false”/>

</commands>

</customUI>

• If you have two or more workbooks (or add-ins) that add controls to the same custom Ribbon group, you must make sure that they both use the same namespace. Do this in the <CustomUI> tag at the top of the RibbonX code.

Creating an Old-Style Toolbar

If you find that customizing the Ribbon is just too much work, you may be content to create a simple custom toolbar using the pre–Excel 2007 CommandBar object. This technique is perfectly suitable for any workbook that only you will be using. It's an easy way to provide quick access to a number of macros.

In this section, I provide boilerplate code that you can adapt as needed. I don't offer much in the way of explanation. For more information about CommandBar objects, search the Web or consult a previous edition of this book. CommandBar objects can be much more powerful than the example presented here.

Limitations of old-style toolbars in Excel 2010

If you decide to create a toolbar for Excel 2010, be aware of the following limitations:

• The toolbar can't be free-floating.

• It will always appear in the Add-Ins⇒Custom Toolbars group (along with any other toolbars).

• Some of the CommandBar properties and methods are ignored by Excel.

Code to create a toolbar

The code in this section assumes that you have a workbook with two macros (named Macro1 and Macro2). It also assumes that you want the toolbar to be created when the workbook is opened, and deleted when the workbook is closed.

Unlike Ribbon modifications, custom toolbars are visible regardless of which workbook is active.

In the ThisWorkbook code module, enter the following procedures. The first one calls the procedure that creates the toolbar when the workbook is opened. The second calls the procedure to delete the toolbar when the workbook is closed:

Private Sub Workbook_Open()

Call CreateToolbar

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteToolbar

End Sub

In Chapter 19, I describe a potentially serious problem with the Workbook_BeforeClose event. Excel's “Do you want to save . . .” prompt displays after the Workbook_BeforeClose event handler runs. So if the user clicks Cancel, the workbook remains open, but the custom menu items have already been deleted. In Chapter 19, I also present a way to get around this problem.

The CreateToolbar procedure follows:

Const TOOLBARNAME As String = “MyToolbar”

Sub CreateToolbar()

Dim TBar As CommandBar

Dim Btn As CommandBarButton

‘ Delete existing toolbar (if it exists)

On Error Resume Next

CommandBars(TOOLBARNAME).Delete

On Error GoTo 0

‘ Create toolbar

Set TBar = CommandBars.Add

With TBar

.Name = TOOLBARNAME

.Visible = True

End With

‘ Add a button

Set Btn = TBar.Controls.Add(Type:=msoControlButton)

With Btn

.FaceId = 300

.OnAction = “Macro1”

.Caption = “Macro1 Tooltip goes here”

End With

‘ Add another button

Set Btn = TBar.Controls.Add(Type:=msoControlButton)

With Btn

.FaceId = 25

.OnAction = “Macro2”

.Caption = “Macro2 Tooltip goes here”

End With

End Sub

A workbook that contains this code is available on the companion CD-ROM. The filename is old-style toolbar.xlsm.

Figure 22-17 shows the two-button toolbar.

FIGURE 22-17: An old-style toolbar, located in the Custom Toolbars group of the Add-Ins tab.

I use a module-level constant, TOOLBAR, which stores the toolbar's name. This name is also used in the DeleteToolbar procedure, so using a constant ensures that both procedures work with the same name.

The procedure starts by deleting the existing toolbar that has the same name (if such a toolbar exists). Including this statement is useful during development and also eliminates the error you get if you attempt to create a toolbar using a duplicate name.

The toolbar is created by using the Add method of the CommandBars object. The two buttons are added by using the Add method of the Controls object. Each button has three properties:

• FaceID: A number that determines the image displayed on the button. Chapter 23 contains more information about FaceID images.

• OnAction: The macro that is executed when the button is clicked.

• Caption: The ScreenTip that appears when you hover the mouse pointer over the button.

Rather than set the FaceID property, you can set the Picture property using any of the imageMso images. For example, the statement below displays a green check mark:

.Picture = Application.CommandBars.GetImageMso _

(“AcceptInvitation”, 16, 16)

For more information about imageMso images, see the sidebar, “Using imageMso images.”

When the workbook is closed, the Workbook_BeforeClose event procedure fires, which calls DeleteToolbar:

Sub DeleteToolbar()

On Error Resume Next

CommandBars(TOOLBARNAME).Delete

On Error GoTo 0

End Sub