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:

The CommandBar Object in Excel 2007

Excel 97 introduced a completely new way of handling toolbars and menus. These UI elements are CommandBar objects. What’s commonly called a toolbar is actually one of three types of command bars:

• Toolbar: This is a bar with one or more clickable controls.

• Menu bar: The two built-in menu bars are Worksheet menu bar and Chart menu bar.

• Shortcut menu: This is the menu that pops up when you right-click an object.

For compatibility purposes, Excel 2007 still supports the CommandBar object — but its functionality has been significantly deprecated. It’s no longer possible for an end user to create a custom toolbar. However, a VBA programmer can still create and work with CommandBar objects (see “Creating an Old-Style Toolbar,” later in this chapter). The problem, however, is that many of the CommandBar properties and methods are simply ignored in Excel 2007. For example, every toolbar or customized menu appears in the Add-Ins tab of the Ribbon. Properties that control a toolbar’s dimensions and position no longer work. In addition, floating toolbars are no longer possible.

The accompanying figures show a customized menu and toolbar in Excel 2003, and the same menu and toolbar in Excel 2007. Although these UI elements are still functional in Excel 2007, it’s clearly not what the developer (me!) had in mind. Needless to say, many VBA developers will want to redo the UI for their applications.

In this chapter, I present a simple example of creating a custom toolbar by using the CommandBar object (see “Creating an Old-Style Toolbar”). For complete details on creating custom menus and toolbars with the CommandBar object, consult the previous edition of this book.

Customizing shortcut menus is still supported in Excel 2007, and I cover that topic in Chapter 23.

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.

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

Tip

If you would 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.

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")

Determining the name of a particular control is a manual task. First, display the Customize tab of the Office 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 (see Figure 22-2)

Figure 22-2. Using the Customize 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.

CD-ROM

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.

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 of these methods take one argument: idMso, which represents the name of the command.

Some of these methods are fairly worthless. 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 new feature that facilitates selecting objects on a worksheet):

 Application.CommandBars.ExecuteMso("SelectionPane")

The following statement displays the Paste Special dialog box:

 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/Hide group):

 MsgBox Application.CommandBars.GetPressedMso("ViewFormulaBar")

Note, however, that your code cannot 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 image 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

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:

C:Documents and Settings<username>Local Settings Application DataMicrosoftOffice

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.

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

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.

CD-ROM

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 previous versions of Excel.

Tip

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 is not worth the effort, don’t despair. Tools will be available that make the process much less tedious than I’ve described. As I write this book, only one such tool is available: Office 2007 Custom UI Editor, written by Trang Luu (see Figure 22-6). 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.

Figure 22-6. The Office 2007 Custom UI Editor.

This section provides some additional details about the ribbon modification.xlsm workbook I discuss in the previous section.

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

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.

Using imageMso Images

Microsoft Office 2007 provides more than 2,500 named images that are associated with various commands. You can specify any of these images for your custom Ribbon controls — if you know the image’s name.

The accompanying figure shows a workbook that contains the names of all the imageMso images. Scroll through the image names, and you see 50 images at a time (in small or large size), beginning with the image name in the active cell. This workbook, named mso image browser.xlsm, is available on the companion CD-ROM.

You can also use these images in an Image control placed on a UserForm. The statement below assigns the imageMso image named ReviewAcceptChanges to the Picture property of a UserForm Image control named Image1. The size of the image is specified as 32 x 32 pixels.

 Image1.Picture = Application.CommandBars. _
   GetImageMso("ReviewAcceptChange", 32, 32)

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.

Note

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

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.

Note

Although Excel has more than 1,700 commands, it does not 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-7 shows the new control.

Figure 22-7. 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:

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 is not 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.

CD-ROM

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.

Figure 22-8 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-8. A new Ribbon tab with four groups of controls.

CD-ROM

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">
    </tabs>
</ribbon>

Tip

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. In addition, all the Office button menu commands are hidden except for New, Open, Excel Options, and Exit.

   <ribbon startFromScratch="true" >

<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>

Creating controls

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

Figure 22-9. 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. It’s 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-10 shows the controls in the second group, labeled More Stuff.

Figure 22-10. 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

Note

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-11). To include a built-in control in a custom group, you just need to know its name (the idMso parameter).

Figure 22-11. 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-12 shows the final group of controls, which consists of two galleries.

Figure 22-12. 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-13 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):

Figure 22-13. A gallery that displays month names, plus a button.

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

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

Figure 22-14. A gallery of 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.

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 is not 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-15 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-15. 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

CD-ROM

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

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

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

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.

Note

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

Cross-Reference

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

CD-ROM

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

Figure 22-16 shows the two-button toolbar.

Figure 22-16. 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:

Tip

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