Adding a button to the Ribbon

The simplest way to use the Ribbon to execute your code is to add your macro to a custom group using Excel’s Customize Ribbon interface. In a new workbook, insert a Module and add the following simple procedure:

Public Sub HelloWorld()
    MsgBox"Hello World!"
End Sub

Return to Excel, right click anywhere on the Ribbon, and choose Customize the Ribbon to display the Customize Ribbon tab in the Excel Options dialog box. The Customize Ribbon tab primarily consists of two lists. The list on the left contains all the possible commands, and the list on the right shows what the Ribbon currently looks like.

At the top of these lists are drop-down boxes that allow you to filter them, making the command you’re looking for easier to find. From the drop-down above the commands list, choose Macros as shown in Figure 17.2. Now the left list shows all the macros that are available to add to the Ribbon, including the HelloWorld procedure you just created.

You can’t add your macro to just anywhere on the Ribbon. Excel prevents you from changing its built-in groups. To add your macro, you must create a custom group. Follow these steps to add the HelloWorld procedure to a custom group on the Home tab.

1. Select the Home tab in the right list of the Customize Ribbon tab. If you don’t see the Home tab, select Main Tabs from the drop-down above this list.
2. Click the New Group button below the list to add a custom group to the Home tab.
3. The new group is named New Group (Custom) by default. Click the Rename button to change the group’s name to MyGroup.
4. With the custom group selected, select the HelloWorld entry in the left list and click the Add>> button. Your HelloWorld macro now appears below the custom group.
5. Select the HelloWorld entry in the right list and click the Rename button. In the Rename dialog box, you can change the label of the control and change the icon from the default Macro icon. Figure 17.3 shows the Rename dialog box where the blue information icon is selected and the Display Name is changed to include a space between Hello and World.
6. Click OK to close the Excel Options dialog box.

The Home tab now contains a custom group called MyGroup, and that group contains one control labelled Hello World. Figure 17.4 shows the new control and the message box that’s displayed when it’s clicked.

Adding a button to the Quick Access Toolbar

Another method for accessing your macros is to add them to the Quick Access Toolbar (QAT). The QAT is a list of buttons that’s always visible regardless of which tab is showing on the Ribbon. By default, the QAT is above the tabs on the Ribbon, but it can also be shown below the Ribbon. If you prefer to show the QAT below the Ribbon, click the small down arrow on the right of the QAT and choose Show Below the Ribbon from the menu.

By default, the QAT shows the Save, Undo, and Redo commands. In this example, we’ll add the HelloWorld procedure from the preceding section to the QAT. The steps are similar to adding a button to the Ribbon.

Click the QAT down arrow and choose More Commands from the menu to display the Quick Access Toolbar tab of the Excel Options dialog. Note how similar this tab is to the Customize Ribbon tab from the preceding section. It has a list of commands on the left and the current state of the QAT on the right.

Next, select Macros from the drop-down box above the left list. The HelloWorld procedure now appears in the list. Select HelloWorld from the left list and click the Add>> button to add it to the QAT (see Figure 17.5). Unlike customizing the Ribbon, there is no Rename button. To customize a QAT button, click the Modify button to choose an icon and change the name. The QAT doesn’t actually display names. Changing Display Name in the Modify Button dialog changes what’s shown in the tooltip when you hover over the button.

When you return to Excel’s main window, the QAT will include a fourth button that executes your HelloWorld procedure. Figure 17.6 shows the QAT below the Ribbon and the results of clicking the new button.

Understanding the limitations of Ribbon customization

Now that you have a custom button on both the Ribbon and the QAT, you can easily execute the HelloWorld procedure. When you save and close the workbook that contains HelloWorld, the buttons on the Ribbon and QAT are still there. If you click either of those buttons when the workbook is closed, Excel will attempt to open the workbook. If Excel can’t find it because you moved or renamed the workbook, you get a message that Excel can’t find your macro (see Figure 17.7).

One way to prevent this message is include your macro in an add-in that’s always loaded. See Chapter 16 for how to create an add-in. If you only want the buttons to appear when the workbook is opened or you want to use Ribbon controls other than the Button control, you have to create a custom Ribbon in your workbook.

Adding a button to an existing tab

This section contains a step-by-step walkthrough that will create two controls in a custom group on the Data tab of the Ribbon. You’ll use the Custom UI Editor for Microsoft Office, an application created by Microsoft, to insert the XML for the new Ribbon into a workbook.

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

1. Create a new Excel workbook and insert a standard module.
2. Save the workbook as macro-enabled and name it ribbon modification.xlsm.
3. Close the workbook.
4. Launch the Custom UI Editor for Microsoft Office.
5. Open ribbon modification.xlsm by clicking the Open button on the Custom UI Editor toolbar and navigating to the file.
6. From the Insert menu, choose Office 2007 Custom UI Part. This will add a customUI.xml entry under your workbook in the tree view on the left.
7. In the main window, type the code in Figure 17.8. XML is case-sensitive, so be sure to type it exactly as displayed.
8. Click the Validate button on the toolbar to make sure the XML is valid. The editor will display a Custom UI is well formed message if there are no errors.
9. Click the Generate Callbacks button on the toolbar. Figure 17.9 shows the procedures you’ll need for the buttons to work. Copy these procedures to the Clipboard so you can paste them into the workbook later.
10. Double-click the customUI.xml entry in the tree view to return to the XML window.
11. Choose File ➜ Save, and then choose File ➜ Close.
12. Activate Excel and open the workbook.
13. Press Alt+F11 to open the VBE and paste the callback procedures you copied in step 9 into the module you created in step 1.
14. Add a MsgBox line to each procedure, as shown in Figure 17.10.
15. Return to Excel, activate the Data tab, and click your new buttons to test that they work (see Figure 17.11).

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 before Excel 2007.

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

<tab idMso="TabData">

Adding a check box to an existing tab

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.

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 deselected state. If you activate a worksheet that displays page breaks, the control should be selected. 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="FileName_Group1" label="Custom">
    <checkBox id="FileName_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 17.12 shows the new control, placed in a group named Custom.

Public MyRibbon As IRibbonUI
 
Sub Initialize(Ribbon As IRibbonUI)
'   Executed when the workbook loads
    Set MyRibbon = Ribbon
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    CheckPageBreakDisplay
End Sub

Sub CheckPageBreakDisplay()
'   Executed when a sheet is activated
    MyRibbon.InvalidateControl ("Checkbox1")
End Sub

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

Sub TogglePageBreakDisplay(control As IRibbonControl, pressed As Boolean)
'   Executed when check box is clicked
    On Error Resume Next
    ActiveSheet.DisplayPageBreaks = pressed
End Sub

Ribbon controls demo

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

<ribbon>
  <tabs>
    <tab id="FileName_CustomTab" label="My Stuff">
    </tab>
  </tabs>
</ribbon>

<group  id="FileName_grpInfo" label="Information">
</group>
 
<group  id="FileName_grpMath" label="Math">
</group>
 
<group  id="FileName_grpFeedback" label="Feedback">
</group>
 
<group  id="FileName_grpBuiltIn" label="Built In Stuff">
</group>
 
<group  id="FileName_grpGalleries" label="Galleries">
</group>

Creating controls

Following is the RibbonX code that creates the controls in the first group (Information). Figure 17.14 shows these controls on the Ribbon.

<group id="FileName_grpInfo" label="Information">
  <labelControl id="FileName_lblUser" getLabel="getlblUser"/>
  <labelControl id="FileName_lblDate" getLabel="getlblDate"/>
</group>

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

Sub getlblUser(control As IRibbonControl, ByRef returnedVal)
    returnedVal ="Hello" & Application.UserName
End Sub
 
Sub getlblDate(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 user’s name and the date.

Figure 17.15 shows the controls in the second group, labeled Math.

The RibbonX for the Math group follows:

<group id="FileName_grpMath" label="Math">
  <editBox id="FileName_ebxSquare"
    showLabel="true"
    label="The square of"
    onChange="ebxSquare_Change"/>
 
  <labelControl id="FileName_lblSquare"
    getLabel="getlblSquare"/>
  <separator id="FileName_sepMath"/>
  <button id="FileName_btnCalc"
    label="Calculator"
    size="large"
    onAction="ShowCalculator"
    imageMso="Calculator"/>
</group>

The editBox control has an onChange callback procedure named ebxSquare_Change, which updates a label to display the square of the number entered. The ebxSquare_Change procedure is:

Private sq As Double
Sub ebxSquare_Change(control As IRibbonControl, text As String)
    sq = Val(text) ^ 2
    MyRibbon.Invalidate
End Sub

The label control showing the result is updated when MyRibbon is invalidated. Invalidating the Ribbon causes all the controls to reinitialize. This procedure sets the sq variable to the square of the number entered, which is used by the label in the next procedure.

The label control has a getLabel callback procedure named getlblSquare. When the Ribbon is invalidated, this procedure is run. For an example of how to invalidate the Ribbon, see the “Adding a check box to an existing tab” section earlier in this chapter.

Sub getlblSquare(control As IRibbonControl, ByRef returnedVal)
    returnedVal ="is" & sq
End Sub

The separator control, sepMath, adds a vertical line to separate the squaring controls with the last control. The last control in this 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 17.16 shows the controls in the third group, labeled Feedback.

The RibbonX code for the second group is as follows:

<group  id="FileName_grpFeedback" label="Feedback">
  <toggleButton id="FileName_ToggleButton1"
    size="large"
    imageMso="FileManageMenu"
    label="Toggle Me"
    onAction="ToggleButton1_Click" />
 
  <checkBox id="FileName_Checkbox1"
    label="Checkbox"
    onAction="Checkbox1_Change"/>
 
  <comboBox id="FileName_Combo1"
    label="Month"
    onChange="Combo1_Change">
    <item id="FileName_Month1" label="January" />
    <item id="FileName_Month2" label="February"/>
    <item id="FileName_Month3" label="March"/>
    <item id="FileName_Month4" label="April"/>
    <item id="FileName_Month5" label="May"/>
    <item id="FileName_Month6" label="June"/>
    <item id="FileName_Month7" label="July"/>
    <item id="FileName_Month8" label="August"/>
    <item id="FileName_Month9" label="September"/>
    <item id="FileName_Month10" label="October"/>
    <item id="FileName_Month11" label="November"/>
    <item id="FileName_Month12" label="December"/>
  </comboBox>
</group>

The group contains a toggleButton, a checkBox, and a comboBox control. These controls are straightforward. Each has an associated callback procedure that simply displays the status of the control:

Sub ToggleButton1_Click(control As IRibbonControl, pressed As Boolean)
    MsgBox"Toggle value:" & pressed
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 controls in the fourth group consist of built-in controls, as shown in Figure 17.17. To include a built-in control in a custom group, you just need to know its name (the idMso parameter).

The RibbonX code is:

<group id="FileName_grpBuiltIn" 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 17.18 shows the final group of controls, which consists of two galleries.

The RibbonX code for these two gallery controls is:

<group id="FileName_grpGalleries" label="Galleries">
  <gallery id="FileName_galAppointments"
    imageMso="ViewAppointmentInCalendar"
    label="Pick a Month:"
    columns="2" rows="6"
    onAction="MonthSelected">
    <item id="FileName_January" label="January"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_February" label="February"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_March" label="March"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_April" label="April"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_May" label="May"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_June" label="June"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_July" label="July"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_August" label="August"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_September" label="September"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_October" label="October"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_November" label="November"
      imageMso="QuerySelectQueryType"/>
    <item id="FileName_December" label="December"
      imageMso="QuerySelectQueryType"/>
    <button id="FileName_Today"
      label="Today..."
      imageMso="ViewAppointmentInCalendar"
      onAction="ShowToday"/>
  </gallery>
  <gallery id="FileName_galPictures"
    label="Sample Pictures"
    columns="4"
    itemWidth="100" itemHeight="125"
    imageMso="Camera"
    onAction="galPictures_Click"
    getItemCount="galPictures_ItemCount"
    getItemImage="galPictures_ItemImage"
    size="large"/>
</group>

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

The second gallery, shown in Figure 17.20, displays eight images, saved as JPG images.

These images are stored in a folder named demopics in the same folder as the workbook. The gallery uses the getItemImage callback procedure to fill the images. When the Ribbon is first loaded, the onLoad callback procedure, shown below, creates an array of image files in the directory, counts them, and stores the information in module-level variables, aFiles() and ImgCnt, so the other callback procedures can read them.

Private ImgCnt As Long
Private aFiles() As String
Private sPath As String
 
Sub ribbonLoaded(ribbon As IRibbonUI)
    Set MyRibbon = ribbon
 
    Dim sFile As String
    sPath = ThisWorkbook.Path &"demopics"
    sFile = Dir(sPath &"*.jpg")
    Do While Len(sFile) > 0
        ImgCnt = ImgCnt + 1
        ReDim Preserve aFiles(1 To ImgCnt)
        aFiles(ImgCnt) = sFile
        sFile = Dir
    Loop
End Sub

When the gallery is clicked, the getItemCount callback procedure, named galPictures_ItemCount, reads the ImgCnt variable, and galPictures_ItemImage is called that many times. Each time it’s called, the index argument is increased by one. VBA’s LoadPicture function is used to insert the images into the gallery.

Sub galPictures_ItemCount(control As IRibbonControl, _
    ByRef returnedVal)
 
    returnedVal = ImgCnt
End Sub
 
Sub galPictures_ItemImage(control As IRibbonControl, _
    index As Integer, ByRef returnedVal)
 
    Set returnedVal = LoadPicture(sPath & aFiles(index + 1))
End Sub

Note that dynamic controls, like galleries, start their index at zero.

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 17.21 shows the menu that appears when Sheet1 is active. When a sheet is activated, a VBA procedure sends XML code specific to 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.

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="FileName_CustomTab" label="Dynamic">
        <group id="FileName_group1" label="Dynamic Menu Demo">
          <dynamicMenu id="FileName_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 “Adding a check box to an existing tab”): 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). Note 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.

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).Value &""
    Next r
    returnedVal = XMLcode
End Sub

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 the display of error messages. 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. Custom controls do not scale in Excel 2007, but they do in Excel 2010 and later.
• You cannot add or remove controls from a built-in Ribbon group.

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

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

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 if the workbook contains a table (created by choosing Insert ➜ Tables ➜ Table), the ViewCustomViews control is disabled. In other words, a workbook can use either the Custom Views feature or the Tables feature — but not both.

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 17.22).

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.

Working with the Ribbon

The preceding section provided an example of using the GetEnabledMso method of the CommandBars object. Following is a list of all methods relevant to working with the Ribbon via the CommandBars object. All these methods take one argument: idMso, which is a String data type and represents the name of the command. You must know the name — using index numbers is not possible.

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

The VBA statement that follows toggles the Selection task 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"

To toggle the formula bar, use this statement:

Application.CommandBars.ExecuteMso"ViewFormulaBar"

To make sure the formula bar is visible, use this code:

With Application.CommandBars
  If Not .GetPressedMso("ViewFormulaBar") Then .ExecuteMso"ViewFormulaBar"
End With

To make sure the formula bar is not visible, use this code:

With Application.CommandBars
  If .GetPressedMso("ViewFormulaBar") Then .ExecuteMso"ViewFormulaBar"
End With

Or don’t bother with the Ribbon and set the DisplayFormulaBar property of the Application object to either True or False. This statement displays the formula bar (or has no effect if the formula bar is already visible):

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 in 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+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}"

To avoid the display of keytips, turn off screen updating:

Application.ScreenUpdating = False
Application.SendKeys"%h{F6}"
Application.ScreenUpdateing=True

Limitations of old-style toolbars in Excel 2007 and later

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

• The toolbar can’t be free-floating.
• The toolbar will always appear in the Add-Ins ➜ Custom Toolbars group (along with any other toolbars).
• Excel ignores some CommandBar properties and methods.

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.

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

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

Figure 17.23 shows the two-button toolbar.

I use a module-level constant, TOOLBAR, which stores the toolbar’s name. This name is used also 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 18 contains more information about FaceID images.
• OnAction: The macro executed when the button is clicked.
• Caption: The screen tip that appears when you hover the mouse pointer over the button.

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

Note that the toolbar is not deleted from workbook windows that were opened after the toolbar was created.