Excel VBA Programming For Dummies, 4th Edition (2015)

Part IV. Communicating with Your Users

Chapter 19. Accessing Your Macros through the User Interface

In This Chapter

Getting to know ways to customize the Ribbon

Adding icons to the Quick Access Toolbar

Modifying shortcut menus

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 — and Ribbon mania has even spread to other software, including Windows.

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.

Customizing the Ribbon

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 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 later. 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 from the shortcut menu.

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

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. Follow this general procedure:

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

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 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 (to the right of the Zoom group). This new group has five commands.

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

remember Although you cannot remove a built-in tab, you can hide the tab by clearing 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 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 on the Ribbon is visible even when the workbook that contains the macro is not open. Clicking the command opens the workbook that contains the macro and then executes the macro.

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

You can make automatic changes to the Ribbon 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 Excel file, 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.

Get the software

If you'd like to follow along with the Ribbon customization example, you need to download a small program called Custom UI Editor for Microsoft Office. This free program greatly simplifies the process of customizing the Ribbon in Microsoft Office applications. Using this software still requires a lot of work, but it's a lot easier than doing it manually.

The download location tends to change, so you should just search the web for "Custom UI Editor for Microsoft Office" to find the software. It's a small download, and it's free.

Explaining all the intricate details involved in customizing the Ribbon is well beyond the scope of this book. However, I walk you through a quick example that demonstrates the steps required to add a new Ribbon group to the Home tab. The new Ribbon group is named Excel VBA For Dummies, and it contains one button, labeled Click Me. Clicking that button runs a VBA macro named ShowMessage.

tip 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 nearby sidebar “Get the software.”

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, Excel 2013, or Excel 2016.

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

8. Click the Validate button on the toolbar.

If the code has any syntax errors, you 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 on the toolbar).

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

12. Open the workbook in Excel, and 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; paste (or type) the callback procedure that was generated in Step 9; and add a MsgBox statement, so you'll know whether 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, and click the new button on the Ribbon.

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

Figure 19-3: RibbonX code displayed in the Custom UI Editor.

Figure 19-4: The VBA callback procedure that is executed by clicking the Ribbon button.

Figure 19-5: Proof that adding a new Ribbon command using XML is actually possible.

remember 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 (the software I used has not been updated for Office 2013 or Office 2016). For maximum compatibility, use the Excel 2007 Custom UI Part.

You probably realize that modifying the Ribbon 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 book 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). Specifically, I describe how to customize shortcut menus. It’s not as slick as the Ribbon, but it’s a lot easier, and it still provides quick access to your macros.

Adding a button to the Quick Access toolbar

If you create a macro that you use frequently, you may want to add a new button to the Quick Access toolbar. Doing so is easy, but you must do it manually. The Quick Access toolbar is intended to be customized by end users only — not programmers. Here’s how to do it:

1. Right-click the Quick Access toolbar and select Customize Quick Access toolbar from the shortcut menu to display the Quick Access toolbar tab of the Excel Options dialog box.

2. From the drop-down list labeled Choose Commands From, select Macros.

3. Select your macro from the list.

4. Click the Add button to add the macro to the Quick Access toolbar list on the right.

5. If you like, click the Modify button to change the icon and (optionally) the display name.

When you click a macro button on the Quick Access toolbar, the workbook that contains the macro is opened (if it's not already open). And the macro can be executed when any workbook is open.

You'll also find an option to display the Quick Access toolbar button only when a particular workbook is open. Before you add the macro, use the drop-down control at the top-right of the Excel Options dialog box and specify the workbook name rather than For All Documents (Default).

If you have macros that are useful for many workbooks, storing them in your Personal Macro Workbook is a good idea.

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 (like later versions) simply dumps your customized menus and toolbars into a catch-all 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 shortcut menus (which also uses the CommandBar object) still works as it always has — well, sort of. See “What's different in Excel 2013 and Excel 2016?” later in this chapter.

Bottom line? The CommandBar object is not very useful anymore, but it remains the only way to customize shortcut menus.

Adding a new item to the Cell shortcut menu

This section contains sample code that adds a new item to the shortcut menu that appears when you right-click a cell. I don’t bother with the technical details, but you should be able to adapt these examples to your needs.

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.

tip This example is available at this book’s website.

The AddToShortcut procedure adds a new menu item to the Cell shortcut menu. You can adapt it to point to your own macros by changing the Caption and OnAction properties of the object named NewControl.

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

remember 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-6 shows the new menu item displayed after you right-click a cell.

Figure 19-6: The Cell shortcut menu showing a custom menu item: Change Case.

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

What's different in Excel 2013 and Excel 2016?

If you've used VBA to work with shortcut menus in Excel 2007 or earlier, you need to be aware of a significant change.

In the past, if your code modified a shortcut menu, that modification was in effect for all workbooks. For example, if you added a new item to the Cell right-click menu, that new item would appear when you right-clicked a cell in any workbook (plus other workbooks that you open later on). In other words, shortcut-menu modifications were made at the application level.

Excel 2013 and Excel 2016 use a single document interface, and that affects shortcut menus. Changes that you make to shortcut menus affect only the active workbook window. When you execute the code that modifies the shortcut menu, the shortcut menu for windows other than the active window will not be changed. This is a radical departure from how things used to work.

Another twist: If the user opens a workbook (or creates a new workbook) when the active window displays the modified shortcut menu, the new workbook also displays the modified shortcut menu. In other words, new windows display the same shortcut menus as the window that was active when the new windows were opened.

Bottom line: In the past, if you opened a workbook or add-in that modified shortcut menus, you could be assured that the modified shortcut menus would be available in all workbooks. You no longer have that assurance.