Excel 2016 Power Programming with VBA (2016)

Part IV. Developing Excel Applications

Chapter 17. 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 boilerplate code for creating an old-style toolbar

Ribbon Basics

Beginning with Microsoft Office 2007, the primary user interface was changed from menus and toolbars to the Ribbon. While there are similarities between toolbars and the Ribbon, the Ribbon is radically different, particularly when it comes to VBA.

The Ribbon is made up of a hierarchy of tabs, groups, and controls. The tabs appear across the top. Each tab consists of one or more groups and each group consists of one or more controls.

·     Tabs: The top object in the Ribbon hierarchy. You use tabs to separate the most fundamental operations into logical groups. The default Ribbon contains the Home, Insert, Page Layout, Formulas, Data, Review, and View tabs. You can add controls to existing tabs or create new tabs. For example, you might make a new tab with your company’s name that contains controls for code that’s specific to your company’s operations.

·     Groups: The second highest object in the Ribbon hierarchy. Groups contain any of the number of different types of controls and are used to logically separate operations supported by a Ribbon tab. The default Formulas tab contains the Function Library, Defined Names, Formula Auditing, and Calculation groups. You don’t have include only related controls in a group, but it helps the user navigate the Ribbon more easily if you do.

·     Controls: This level of the hierarchy is where the action is. You interact with Excel or your custom VBA code through controls. The Ribbon supports a variety of controls, many of which are discussed in this chapter.

The Ribbon supports many types of controls. While I don’t discuss every type of control in this chapter, I do discuss the ones you are likely to use. If you’re used to the older menus and toolbars, you’ll appreciate the flexibility that the Ribbon controls offer. Figure 17.1shows the default Page Layout tab with a good selection of control types. A brief description of some of the controls is listed below:

·     Button: The Button control is the most basic Ribbon control and will be most familiar to you if you used the older toolbar user interface. You click a button and it performs an action. The Cut button on the Home tab performs the built-in cut action. Your custom buttons can be used to execute a macro that you’ve written.

·     SplitButton: The SplitButton control is similar to the Button control, but with an added feature. It is split, either horizontally or vertically, into a button part and a list part. You can click the button part to perform an action, just like the Button control. The list part, represented by an arrow, shows a list of similar buttons. The Paste SplitButton on the Home tab is a good example. The button part performs the normal paste operation. If you click on the arrow to show the list, you can choose a different paste operation likePaste Values or Paste Formatting.

·     CheckBox: The CheckBox control is similar to a check box on a Userform. It appears as an empty box when unchecked and contains a checkmark when checked. The View Gridlines controls in the Page Layout ➜Sheet Options group is a good example of a checkbox.

·     ComboBox: The ComboBox control is another familiar control if you’ve used Userforms. Like the Userform control with the same name, you can type text into the text box part of a ComboBox (called an EditBox in the Ribbon) or select an item from a list. TheNumberFormat control in the Home ➜ Number group is a good example of a ComboBox control. For example, you can type Currency directly in the text box portion or click the drop down arrow and select a number format from the list.

·     Menu: The Menu control displays a list of other controls. You can include a Button, SplitButton, CheckBox, or even another Menu control in the list. It differs from a SplitButton because when you click it, it always displays the list. That is, it does not have the option of a having default control. The Conditional Formatting control on the Home tab is an example of a Menu control.

Screenshot shows the page layout bar in an excel where the menu control, button control, combox control, checkbox control and split button control are represented.

Figure 17.1 The Page Layout tab contains many different control types.

There are several other controls offered by the Ribbon, including the ToggleButton, Gallery, EditBox, dynamicMenu, and Label. Some of these controls are used in this chapter. To learn more about these and the other controls, visit Microsoft’s website athttps://msdn.microsoft.com/en-us/library/bb386089.aspx.

Customizing the Ribbon

Excel provides a couple of ways for you to add your macros to the Ribbon. These methods don’t give you the flexibility that creating a custom Ribbon does, but what they lack in customization, they make up for in simplicity.

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

inline On the Web

This workbook, named Custom Ribbon and QAT.xlsm, is available on the book’s website.

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.

Image described by surrounding text.

Figure 17.2 The Customize Ribbon tab allows you to add macros to the Ribbon.

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.

Image described by surrounding text.

Figure 17.3 The Rename dialog lets you choose an icon for your Ribbon button.

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.

Screenshot shows the right end of the menu bar in an excel along with a pop up window showing hello world message and an OK button.

Figure 17.4 The custom Ribbon button executes the HelloWorld macro.

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.

Image described by surrounding text.

Figure 17.5 You can add a macro to the Quick Access Toolbar.

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.

Screenshot shows the left end of the icons in home tab of an excel along with a pop up window showing hello world message and an OK button.

Figure 17.6 The new QAT button executes your macro.

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

Image described by surrounding text.

Figure 17.7 Excel can’t find the macro associated with the Ribbon button.

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 Buttoncontrol, you have to create a custom Ribbon in your workbook.

Creating a Custom Ribbon

You can’t perform any Ribbon modifications using VBA. Rather, you must write RibbonX code and insert the code into the workbook file — outside of Excel. You can, however, create VBA macros that are executed when a custom Ribbon control is activated.

RibbonX code is Extensible Markup Language (XML) that describes the controls, where on the Ribbon they’re displayed, what they look like, and what happens when they’re activated. This book only covers a small portion of RibbonX — the topic is complex enough to be the subject of an entire book.

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.

inline On the Web

You can download a free copy of the Custom UI Editor for Microsoft Office from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/ 2009/08/06/7293.aspx.

inline See your errors

Before you do any work with Ribbon customization, you should enable the display of RibbonX errors. Access the Excel Options dialog box (File ➜ Options) and click the Advanced tab. Scroll down to the General section and select Show Add-in User Interface Errors.

When this setting is enabled, RibbonX errors (if any) are displayed when the workbook opens — which is helpful for debugging.

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

Image described by caption.

Figure 17.8 XML to create two buttons in a custom group.

Screenshot shows a ribbon modification window with customerUIxml selected on the left partition and Callback function for Button 1 and Button 2 on the right side.

Figure 17.9 The editor generates VBA code to use in your workbook.

Screenshot shows a ribbon modification window with two dropdowns where general and GoodbyeWorld are selected along with Callback function for Button 1 and Button 2.

Figure 17.10 Modify the callback procedures in the VBE.

Screenshot shows the right end of the menu bar in an excel along with a pop up window showing hello world message and an OK button.

Figure 17.11 Two new buttons add to the Data tab.

inline On the Web

This workbook, named ribbon modification.xlsm, is available on the book’s website.

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.

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

The RibbonX code

The RibbonX code used in this example is XML. Excel can read this XML and convert it into UI elements, like tabs, groups, and buttons. XML consists of data between opening and closing tags (or, in some cases, within self-closing tags). The first line defines the schema in a customUI tag — this tells Excel how to read the XML. The last line is the closing tag for the customUI tag.

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

</customUI>

Everything between these two tags is interpreted as RibbonX code by Excel. The next line, the ribbon tag, defines that you want to work with the Ribbon. Its closing tag is the second to last line. The XML is hierarchical, just like the Ribbon. You can see in Figure 17.8that the button tags are contained in the group tag, the group tag is contained in a tab tag, the tab tag is contained in the tabs tag, and the tabs tag is contained in the ribbon tag.

Tags also contain attributes. The tab tag contains an idMso attribute that tells Excel which tab to use.

<tab idMso="TabData">

Each built-in tab and group has a unqiue idMso. In this example, TabData tells Excel that you want to work in the built-in Data tab.

inline On the Web

You can get a complete list of idMso values for built-in Ribbon elements on Microsoft’s website at http://www.microsoft.com/en-us/download/confirmation .aspx?id=727.

Custom elements, like the group and button tags, use the id attribute rather than idMso. You can use any value for the id attribute, such as Group1 and Button1 in this example, as long as it’s unique. The following lists the attributes used in the example and a brief description of what they do:

·     idMso: The unique identifier of a built-in UI element.

·     id: A unique identifier, created by you, for custom elements.

·     label: The text that accompanies the control in the Ribbon.

·     size: Button controls can be large, normal, or small.

·     onAction: The name of the VBA procedure to run when the button is clicked.

·     imageMso: The identifier of a built-in image. You can use built-in images on your custom buttons. See the “Using imageMso images” sidebar for more information.

A complete list of attributes for all the UI elements would be too long to list. You can find many examples of RibbonX on the web and change them to suit your needs.

inline Note

RibbonX code is case-sensitive. For example, if you use IMAGEMSO instead of imageMso, your RibbonX code won’t work properly.

Callback Procedures

VBA responds to user actions using Events (see Chapter 6). The Ribbon uses a different technique: callback procedures. The buttons in this example are tied to the VBA code via the OnAction attribute. Most controls have an OnAction attribute, and the action is different for different controls. A button’s action is a click, but a check box’s action is a check or uncheck.

Most attributes have a corresponding callback attribute, generally with a get prefix. For example, the label attribute sets the text that displays for the control. There is also a getLabel attribute. You set the getLabel attribute to the name of a VBA procedure that determines what text is displayed. I’ll discuss dynamic controls later in this chapter, but for now understand that callback procedures are not limited to OnAction.

Both VBA procedures in this example 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 associated with the control.

The VBA callback procedures can be as simple or as complex as necessary.

The CUSTOM UI part

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

Microsoft makes new Custom UI Parts available when it changes the Ribbon in a way that requires one. Don’t look for a 2016 or 2013 Custom UI Part. Those versions of Office continue to use the Office 2010 Custom UI Part.

inline Using imageMso images

Microsoft Office provides more than 1,000 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 for various versions of Office. 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 book’s website.

Image described by surrounding text.

You can also use these images in an Image control placed on a UserForm. The following statement 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)

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.

inline Note

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, the example in this section 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 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.

Image described by caption.

Figure 17.12 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. Note 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)

    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

Note that the returnedVal argument is passed ByRef. This means 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 selected). Otherwise, the control isn’t selected.

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 selects or deselects 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 selects the check box and False if the user deselects the check box. The code sets the DisplayPageBreaks property accordingly.

inline On the Web

This workbook, named page break display.xlsm, is available on the book’s website. The site 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 6 for more information about events and Chapter 20 for more information about class modules.

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.

Screenshot shows the My Stuff bar in an excel where the functions corresponding to information, math, feedback, built in stuff and galleries are displayed.

Figure 17.13 A new Ribbon tab with five groups of controls.

inline On the Web

This workbook, named ribbon controls demo.xlsm, is available on the book’s website.

Creating a new tab

The RibbonX code that creates the new tab is:

<ribbon>

  <tabs>

    <tab id="FileName_CustomTab" label="My Stuff">

    </tab>

  </tabs>

</ribbon>

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

<ribbon startFromScratch="true" >

Creating a Ribbon group

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

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

These pairs of <group> and </group> tags are located between 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 (Information). Figure 17.14 shows these controls on the Ribbon.

Screenshot shows a part of an excel displaying file and home tab along with two labels in the information section.

Figure 17.14 A Ribbon group with two labels.

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

Screenshot shows a part of an excel displaying insert, page layout and formulas tab along with the control for calculator and a text in the math section.

Figure 17.15 An editBox control in a custom Ribbon group.

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.

Screenshot shows a part of an excel displaying data, review and view tab along with icon for toggle me, a checkbox and a dropdown to select month.

Figure 17.16 Three controls in a custom Ribbon group.

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

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

Screenshot shows a part of an excel displaying developer and my stuff tab, where my stuff tab is selected. The controls for copy, paste, switch window, italics, bold and open are represented.

Figure 17.17 This group contains built-in controls.

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.

Screenshot shows a part of an excel displaying controls to pick a month and to take sample pictures along with an icon showing a camera.

Figure 17.18 This Ribbon group contains 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.

Screenshot shows the set of months from January to December displayed in two different columns under the dropdown pick a month.

Figure 17.19 A gallery that displays month names, plus a button.

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.

Image described by surrounding text.

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

Screenshot shows an excel where dynamic tab is selected which displays a dropdown button sheet specific menu listing view the phone log, order office supplies and quit for the day and go home.

Figure 17.21 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="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

inline On the Web

The workbook that contains this example is available on the book’s website in the dynamicmenu.xlsm file.

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.

Using VBA with the Ribbon

As you’ve seen in this chapter, the typical workflow when working with the Ribbon is to create the RibbonX code and use callback procedures to respond to user actions. There are other ways to interact with the Ribbon with VBA, but they are limited.

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.

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

Screenshot lists the excel options where the customize ribbon is selected. Dropdowns for choosing commands and customizing ribbon are also represented.

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

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

inline Caution

As always, use SendKeys as a last resort. And then understand that 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.

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 and is 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 the Excel 2003 edition of this book. CommandBar objects can be much more powerful than the example presented here.

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.

inline Note

If you use Excel 2007 or Excel 2010, custom toolbars are visible regardless of which workbook is active. With Excel 2013 and Excel 2016, however, a custom toolbar is visible only in the workbook in which it was created — and also in new workbooks created while the original 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

inline Cross-Ref

In Chapter 6, I describe a potentially serious problem with the Workbook_BeforeClose event. Excel’s "Do you want to save ..." prompt is displayed 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 6, 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

inline On the Web

A workbook that contains this code is available on the book’s website in the old-style toolbar.xlsm file.

Figure 17.23 shows the two-button toolbar.

Screenshot shows part of an excel displaying the file, home and insert tabs along with a search icon in the custom toolbars section.

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

inline Tip

Rather than set the FaceID property, you can set the Picture property using any of the imageMso images. For example, the following statement 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

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