Chapter 19

Accessing Your Macros through the User Interface

In This Chapter

Getting a sneak peek into customizing the Ribbon using XML

Adding items to a right-click menu

Adding a button to the Quick Access toolbar (manually)

Customizing the Ribbon (manually)

Before Office 2007, there was no such thing as a Ribbon. Back then, people used drop-down menus and toolbars. Now, the Ribbon is the user interface for Microsoft Office.

You might expect to be able to create custom Ribbon commands using VBA. The bad news is that you can’t use VBA to modify the Ribbon. The good news is that you’re not completely out of luck. This chapter describes some of the ways to work with Excel’s user interface.

Ribbon Customization

This section describes ways to customize the Ribbon. You can modify the Ribbon manually, but you cannot make changes to the Ribbon by using VBA. Sad, but true. For example, if you write an application and you’d like to add a few new buttons to the Ribbon, you need to program those changes outside of Excel, using something called RibbonX.

Customizing the Ribbon manually

It’s very easy to make changes to the Ribbon manually, but you must be using Excel 2010 or Excel 2013. If you use Excel 2007, you should just skip this section because it doesn’t apply to you.

You can customize the Ribbon in these ways:

Tabs

• Add a new custom tab.

• Delete custom tabs.

• Add a new group to tab.

• Change the order of the tabs.

• Change the name of a tab.

• Hide built-in tabs.

Groups

• Add new custom groups.

• Add commands to a custom group.

• Remove commands from custom groups.

• Remove groups from a tab.

• Move a group to a different tab.

• Change the order of the groups within a tab.

• Change the name of a group.

That’s a fairly comprehensive list of customization options, but there are some actions that you cannot do (no matter how hard you try):

You cannot remove built-in tabs — but you can hide them.

You cannot remove commands from built-in groups.

You cannot change the order of commands in a built-in group.

You make manual changes to the Ribbon in the Customize Ribbon panel of the Excel Options dialog box (see Figure 19-1). The quickest way to display this dialog box is to right-click anywhere in the Ribbon and choose Customize The Ribbon.

The process of customizing the Ribbon is very similar to customizing the Quick Access toolbar, which is described later in this chapter. The only difference is that you need to decide where to put the command within the Ribbon. The general procedure is

1. Use the drop-down list on the left (labeled Choose Commands From) to display various groups of commands.

2. Locate the command in the list box on the left and select it.

3. Use the drop-down list on the right (labeled Customize The Ribbon) to choose a group of tabs.

Main tabs refer to the tabs that are always visible; Tool tabs refer to the context tabs that appear when a particular object is selected.

4. In the list box on the right, select the tab and the group where you would like to put the command.

You’ll need to click the “plus sign” controls to expand the hierarchical lists.

5. Click the Add button to add the selected command from the left to the group on the right.

Figure 19-1: The Customize Ribbon tab of the Excel Options dialog box.

Keep in mind that you can use the New Tab button to create a new tab, and the New Group button to create a new group within a tab. New tabs and groups are given generic names, so you’ll probably want to give them more meaningful names. Use the Rename button to rename the selected tab or group. You can also rename built-in tabs and groups.

Figure 19-2 shows a custom group (named Text To Speech) that I added to the View tab. This group has five commands.

Figure 19-2: The View tab, after adding a new group named Text To Speech.

Although you cannot remove a built-in tab, you can hide the tab by unchecking the check box next to its name.

Adding a macro to the Ribbon

Fortunately, you can also add macros to the Ribbon. Follow the instructions in the previous section, but in Step 1 choose Macros from the drop-down list on the left. All of the currently available macros are listed, ready to be added to the Ribbon. You just need to decide on a tab and group for the macro.

If you customize the Ribbon to include a macro, the macro command in the Ribbon is visible even when the workbook that contains the macro is not open. Clicking the command will open the workbook that contains the macro.

If you add a button to the Ribbon that executes a macro, that Ribbon modification applies to your copy of Excel only. The Ribbon modifications are not part of the workbook. In other words, if you give your workbook to a colleague, the Ribbon customizations you made will not appear on the colleague’s system.

Customizing the Ribbon with XML

In some situations, you may want to modify the Ribbon automatically, when a workbook or add-in is opened. Doing so makes it easy for the user to access your macro. It also eliminates the need for the user to modify the Ribbon manually by using the Excel Options dialog box.

Making automatic changes to the Ribbon can be done with Excel 2007 and later versions, but it’s not a simple task. Modifying the Ribbon involves writing XML code in a text editor, copying that XML file into the workbook file, editing a bunch of XML files (which also are stashed away inside the new Excel file format, which in reality is nothing more than a zipped container of individual files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file.

Fortunately, software is available to assist you with customizing the Ribbon — but you still need to be on familiar terms with XML.

You can download a sample file from this book’s website, which contains this customization. If you’d like to create it yourself, follow these steps exactly:

1. Create a new Excel workbook.

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

3. Close the workbook.

---

---

4. Launch the Custom UI Editor for Microsoft Office.

If you don’t have this software, you need to find it and install it. Refer to the sidebar, “Get the software,” earlier in this chapter.

5. In the Custom UI Editor, choose File⇒Open and find the workbook you saved in Step 2.

6. Choose Insert⇒Office 2007 Custom UI Part.

Choose this command even if you’re using Excel 2010 or Excel 2013.

7. Type the following code into the code panel (named customUI.xml) displayed in the Custom UI Editor (see Figure 19-3):

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

<ribbon>

<tabs>

<tab idMso='TabHome'>

  <group  id='Group1' label='Excel VBA For Dummies'>

    <button id='Button1'

        label='Click Me'

        size='large'

        onAction='ShowMessage'

        imageMso='FileStartWorkflow' />

    </group>

</tab>

</tabs>

</ribbon>

</customUI>

8. Click the Validate button in the toolbar.

If the code has any syntax errors, you will get a message that describes the problem. If any errors are identified, you must correct them.

9. Click the Generate Callback button.

The Custom UI Editor creates a VBA Sub procedure that is executed when the button is clicked (see Figure 19-4). This procedure is not actually inserted into the workbook, so you need to copy it for later use (or memorize it if you have a good memory).

10. Go back to the customUI.xml module and choose File⇒Save (or click the Save icon in the toolbar).

11. Close the file by using the File⇒Close command.

12. Open the workbook in Excel; click the Home tab.

You should see the new Ribbon group and Ribbon button. But it doesn’t work yet.

13. Press Alt+F11 to activate the VBE.

14. Insert a new VBA module and paste (or type) the callback procedure that was generated in Step 9; add a MsgBox statement, so you’ll know if the procedure is actually being executed.

The procedure is

Sub ShowMessage(control As IRibbonControl)

    MsgBox "Congrats. You found the new ribbon command."

End Sub

15. Press Alt+F11 to jump back to Excel; click the new button on the Ribbon.

If all goes well, you’ll see the MsgBox shown in Figure 19-5.

In the Custom UI Editor, when you choose Insert⇒Office 2007 Custom UI Part, you insert a UI part for Excel 2007. The Custom UI Editor also has an option to insert a UI part for Excel 2010 (and at some point, probably an option to insert a UI part for Office 2013). For maximum compatibility, use the Excel 2007 Custom UI Part.

You only need to insert a UI part for Excel 2010 (or Excel 2013) if you use features that are unique to Excel 2010 or (Excel 2013). You can also have a UI part for each version, but that’s rarely necessary.

As I was writing this, the schema for an Office 2013 custom UI part was not yet available.

You probably realize that making Ribbon modification using XML is not exactly intuitive. Even with a good tool to help (such as the Custom UI Editor), you still need to understand XML. If that sounds appealing to you, search the web or find a book devoted exclusively to customizing the Ribbon interface in Microsoft Office. This isn’t one of them.

---

---

Because this XML stuff is way too complex for the beginner VBA programmer, the remainder of this chapter focuses on UI customization that uses the old method (VBA only). It’s not as slick as the Ribbon, but it’s a lot easier, and it still provides quick access to your macros.

Customizing Shortcut Menus

Before Excel 2007, VBA programmers used the CommandBar object for creating custom menus, custom toolbars, and custom shortcut (right-click) menus.

Beginning with Excel 2007, the CommandBar object is in a rather odd position. If you write code to customize a menu or a toolbar, Excel intercepts that code and ignores many of your commands. Instead of displaying your well-thought-out interface enhancement, Excel 2007 (and later versions) simply dumps your customized menus and toolbars into a catchall Ribbon tab named Add-Ins.

Menu and toolbar customizations end up in the Add-Ins⇒Menu Commands or the Add-Ins⇒Custom Toolbars group. But customizing right-click shortcut menus (which also uses the CommandBar object) still works as it always has. That’s what I cover in the sections that follow.

Commanding the CommandBars collection

Excel supports three types of CommandBars, differentiated by their Type property. For shortcut menus, you’re interested in type 2, also known by its built-in constant name, msoBarTypePopup.

Listing all shortcut menus

The procedure listed here uses the CommandBars collection. It displays, in a worksheet, the names of all shortcut menus — CommandBars that have a Type property of 2 (msoBarTypePopup). For each CommandBar, the procedure lists its Index and Name.

Sub ShowShortcutMenusName()

    Dim Row As Long

    Dim cbar As CommandBar

    Row = 1

    For Each cbar In Application.CommandBars

        If cbar.Type = msoBarTypePopup Then

            Cells(Row, 1) = cbar.Index

            Cells(Row, 2) = cbar.Name

            Row = Row + 1

        End If

    Next cbar

End Sub

Figure 19-6 shows a portion of the result of running this procedure (using Excel 2013), which is available at this book’s website. For example, you see that the shortcut menu named Ply has an index of 42. That oddly named shortcut menu appears when you right-click a sheet tab. You might expect Ply to be named Worksheet Tabs, but that one (CommandBar 36) displays a list of worksheet names.

Referring to CommandBars

In theory, you can refer to a particular CommandBar by using its Index property or its Name property. If you refer to Figure 19-6 you see that the Cell right-click menu has an Index of 10 or 39. This is because the content of the Cell right-click menu differs when Excel is in a different state. Number 10 is the one you get when Excel is in Normal view mode, number 39 shows when you are in Page Break Preview mode. You can refer to the shortcut menu in either of two ways:

Application.CommandBars(10)

or

Application.CommandBars("Cell")

You might think that referring to CommandBars by using their Index property is better. Wrong! The problem is that the index numbers change with each version of Excel. If you write code to add a new item to CommandBar 10, the code will fail in Excel 2010. In that version of Excel, the index number for the Cell right-click menu was 36. So, forget about referring to CommandBars by using index numbers, and refer to them by name.

You can display a built-in, right-click menu using VBA (no right-clicking required!). The following statement, for example, causes the Cell right-click menu to display:

Application.Commandbars("Cell").ShowPopup

Referring to controls in a CommandBar

A CommandBar object contains Control objects, which are buttons, menus, or menu items. The following procedure displays the Caption property for the first Control in the Cell’s right-click menu:

Sub ShowCaption()

    MsgBox Application.CommandBars("Cell"). _

       Controls(1).Caption

End Sub

When you execute this procedure, you see the message box shown in Figure 19-7. The ampersand is used to indicate the underlined letter in the text — the keystroke that will execute the menu item.

In some cases, Control objects on a shortcut menu contain other Control objects. For example, the Sort control on the Cell right-click menu contains other controls.

Each control has a Name and an Id property. You can access a control by using either of these properties (locating a control by its Id is slightly more complex though):

Sub AccessControlByName()

    MsgBox CommandBars("Cell").Controls("Copy").Caption

End Sub

Sub AccessControlById()

    MsgBox CommandBars("Cell").FindControl(ID:=19).Caption

End Sub

Don’t use the Caption property to access a control if you are writing code that may be used by users with a different language version of Excel. The Caption is language-specific, so your code will fail on those users’ systems. Instead, use the FindControl method in conjunction with the Id of the control (which is language-independent). Luckily, the CommandBar names are not internationalized.

Properties of CommandBar controls

CommandBar controls have a number of properties that determine how the controls look and work. This list contains some of the more useful properties for CommandBar controls:

Caption: The text displayed for the control. If the control shows only an image, the Caption appears when you move the mouse over the control.

FaceID: A number that represents a graphics image displayed next to the control’s text.

BeginGroup: True if a separator bar appears before the control.

OnAction: The name of a VBA macro that executes when the user clicks the control.

BuiltIn: True if the control is an Excel built-in control.

Enabled: True if the control can be clicked.

Visible: True if the control is visible. Many of the shortcut menus contain hidden controls.

ToolTipText: Text that appears when the user moves the mouse pointer over the control.

The ShowShortcutMenuItems procedure lists all the first-level controls on every shortcut menu. In addition, it identifies hidden controls by placing their Captions in angle brackets.

Sub ShowShortcutMenuItems()

  Dim Row As Long, Col As Long

  Dim Cbar As CommandBar

  Dim Ctl As CommandBarControl

  Row = 1

  Application.ScreenUpdating = False

  For Each Cbar In Application.CommandBars

    If Cbar.Type = msoBarTypePopup Then

      Cells(Row, 1) = Cbar.Index

      Cells(Row, 2) = Cbar.Name

      Col = 3

      For Each Ctl In Cbar.Controls

        If Ctl.Visible Then

          Cells(Row, Col) = Ctl.Caption

        Else

          Cells(Row, Col) = "<" & Ctl.Caption & ">"

        End If

       Col = Col + 1

     Next Ctl

     Row = Row + 1

   End If

  Next Cbar

End Sub

Figure 19-8 shows a portion of the output when the procedure is run using Excel 2013 (it will be different for Excel 2010). It’s not exactly the most fascinating reading, but you might find it informative — or at least a bit interesting.

The ShowShortcutMenuItems procedure is available at this book’s website. If you run the macro, you can see that many of the shortcut menus contain hidden controls.

This has been a quick and dirty overview of CommandBars. There’s a lot more to CommandBars, of course, but this is as far as I take it in this book. The next section provides some examples that may help clear up any confusion you have.

With the introduction of the new Ribbon user interface, a lot has changed. For amateur developers, some of the changes are for the better, and some for the worse. The possibilities for gaining control over the user interface by using just VBA are now very limited.

VBA Shortcut Menu Examples

This section contains a few examples of using VBA to manipulate the Excel right-click menus — commonly known as shortcut menus. These examples give you an idea of the types of things you can do, and they can all be modified to suit your needs.

Resetting all built-in, right-click menus

The following procedure resets all built-in toolbars to their original state:

Sub ResetAll()

    Dim cbar As CommandBar

    For Each cbar In Application.CommandBars

        If cbar.Type = msoBarTypePopup Then

            cbar.Reset

            cbar.Enabled = True

        End If

    Next cbar

End Sub

This procedure will have no effect unless someone has executed some VBA code that adds items, removes items, or disables shortcut menus.

Adding a new item to the Cell shortcut menu

In Chapter 16, I describe the Change Case utility. You can enhance that utility a bit by making it available from the Cell shortcut menu.

This example is available at this book’s website.

The AddToShortcut procedure adds a new menu item to the Cell shortcut menu. Recall that Excel has two Cell shortcut menus. This procedure modifies the normal right-click menu, but not the right-click menu that appears in Page Break Preview mode.

Sub AddToShortCut()

    Dim Bar As CommandBar

    Dim NewControl As CommandBarButton

    DeleteFromShortcut

    Set Bar = Application.CommandBars("Cell")

    Set NewControl = Bar.Controls.Add _

        (Type:=msoControlButton, ID:=1, _

         temporary:=True)

    With NewControl

        .Caption = "&Change Case"

        .OnAction = "ChangeCase"

        .Style = msoButtonIconAndCaption

    End With

End Sub

---

---

When you modify a shortcut menu, that modification remains in effect until you restart Excel. In other words, modified shortcut menus don’t reset themselves when you close the workbook that contains the VBA code. Therefore, if you write code to modify a shortcut menu, you almost always write code to reverse the effect of your modification.

The DeleteFromShortcut procedure removes the new menu item from the Cell shortcut menu.

Sub DeleteFromShortcut()

    On Error Resume Next

    Application.CommandBars("Cell").Controls _

      ("&Change Case").Delete

End Sub

Figure 19-9 shows the new menu item displayed after right-clicking a cell.

The first actual command after the declaration of a couple of variables calls the DeleteFromShortcut procedure. This statement ensures that only one Change Case menu item appears on the shortcut Cell menu. Try commenting out that line (put an apostrophe at the start of the line) and running the procedure a few times — now don’t get carried away! Right-click a cell, and you can see multiple instances of the Change Case menu item. Get rid of all the entries by running DeleteFromShortcut multiple times (once for each extra menu item).

Finally, you need a way to add the shortcut menu item when the workbook is opened and to delete the menu item when the workbook is closed. Doing this is easy . . . if you’ve read Chapter 11. Just add these two event procedures to the ThisWorkbook code module:

Private Sub Workbook_Open()

    Call AddToShortCut

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call DeleteFromShortcut

End Sub

The Workbook_Open procedure is executed when the workbook is opened, and the Workbook_BeforeClose procedure is executed before the workbook is closed. Just what the doctor ordered.

Disabling a shortcut menu

If you’re so inclined, you can disable an entire shortcut menu. For example, you can make it so that right-clicking a cell does not display the normal shortcut menu. The following procedure, which is executed automatically when the workbook is opened, disables the Cell shortcut menu:

Private Sub Workbook_Open()

    Application.CommandBars("Cell").Enabled = False

End Sub

And here’s its companion procedure that enables the shortcut menu when the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.CommandBars("Cell").Enabled = True

End Sub

Both of these procedures must be in the Code module for the ThisWorkbook object.

Creating a Custom Toolbar

If you check out all the CommandBars available, you may have noticed one called Built-In Menus. This CommandBar contains all the old Excel 2003 menu commands. Figure 19-10 shows part of this giant pop-up menu. All the old commands are there, but they aren’t arranged very well.

In this example, I present a VBA procedure that (kind of) re-creates the old Excel 2003 menu bar. It creates a new CommandBar and then copies the controls from the Built-In Menus CommandBar.

Sub MakeOldMenus()

   Dim cb As CommandBar

   Dim cbc As CommandBarControl

   Dim OldMenu As CommandBar

'  Delete it, if it exists

   On Error Resume Next

   Application.CommandBars("Old Menus").Delete

   On Error GoTo 0

'  Create an old-style toolbar

'  Set the last argument to False for a more compact menu

   Set OldMenu = Application.CommandBars.Add("Old Menus", , True)

'  Copy the controls from Excel's "Built-in Menus" shortcut menu

   With CommandBars("Built-in Menus")

     .Controls("&File").Copy OldMenu

     .Controls("&Edit").Copy OldMenu

     .Controls("&View").Copy OldMenu

     .Controls("&Insert").Copy OldMenu

     .Controls("F&ormat").Copy OldMenu

     .Controls("&Tools").Copy OldMenu

     .Controls("&Data").Copy OldMenu

     .Controls("&Window").Copy OldMenu

     .Controls("&Help").Copy OldMenu

   End With

'   Make it visible. It appears in the Add-Ins tab

   Application.CommandBars("Old Menus").Visible = True

End Sub

Figure 19-11 shows the result of running the MakeOldMenus procedure. Notice that it appears in the Custom Toolbars group of the Add-Ins tab. This menu is, after all, a toolbar. It just looks like a menu.

The new CommandBar is named Old Menus. The procedure starts by deleting that toolbar, if it already exists. Then code copies the “top level” controls from the CommandBar named Built-In Menus to the Old Menus CommandBar. A new CommandBar is hidden by default, so the final statement makes it visible.

The new “menu” system isn’t perfect. A few of the commands don’t work. You’ll also notice that the recent files on the File menu show only placeholders. To get rid of the toolbar, right-click it and choose Delete Custom Toolbar.

This example is available on this book’s website.