VBA and the Ribbon | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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 a particular control.

Following is a list of things that you might like to do with the Ribbon but that are not 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.

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

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.

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 of 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 hove the mouse pointer over the control)

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

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

About the Quick Access Toolbar

In previous versions of Excel, it was relatively easy for end users to change the user interface. They could create custom toolbars that contained frequently used commands, and they could even remove menu items that they never used. Users could display any number of toolbars and move them wherever they liked . Those days are over.

The Quick Access Toolbar (QAT) is the only user-customizable UI element in Excel 2007. It's very easy for a user to add a command to the QAT, so the command is available no matter which Ribbon tab is active. The QAT can't be moved, but Microsoft does allow users to determine whether the QAT is displayed: above or below the Ribbon.

The QAT is not part of the object model, so there is nothing you can do with it using VBA.

The QAT information is stored in an XML file named Excel.qat . The file is located here:

 C:\Documents and Settings\<username>\Local Settings\ Application Data\Microsoft\Office

You can view this file with a text editor or an XML viewer. If you make a copy of the file and rename it with an XML extension, you can even open it with Excel (when prompted for how to open the file, specify as an XML table). However, you cannot modify the Excel.qat file using Excel.