26. Customizing the Ribbon to Run Macros

Out with the Old, In with the New

If you have been working with a legacy version of Excel, one of the first changes you notice when you open Excel 2010 is the Ribbon toolbar that was introduced in Excel 2007. Gone are the menus and toolbars of old. And this change isn’t just visual—the method of modifying custom menu controls was changed just as radically. One of the biggest bonuses of the Ribbon is that you no longer have to worry about your custom toolbar sticking around after the workbook is closed because the custom toolbar is now part of the inner workings of the workbook.

The original CommandBars object still works, but the customized menus and toolbars are all placed on the Add-ins tab. If you had custom menu commands, they will appear on the Menu Commands group, as shown in Figure 26.1. In Figure 26.2, the custom toolbars from two different workbooks appear together on the Custom Toolbars group.

Figure 26.1. Legacy version custom menus will be grouped together under the Menu Commands group.

Figure 26.2. Custom toolbars from legacy versions of Excel appear in the Custom Toolbars group.

If you want to modify the Ribbon and add your own tab, you need to modify the Excel file itself, which isn’t as impossible as it sounds. The new Excel file is actually a zipped file, containing various files and folders. All you need to do is unzip it, make your changes, and you’re done. Okay, it’s not that simple—a few more steps are involved—but it’s not impossible.

Before we begin, go to the File tab and select Options, Advanced, General, and select Show Add-In User Interface Errors. This will allow error messages to appear so that you can troubleshoot errors in your custom toolbar.

Where to Add Your Code: customui Folder and File

Create a folder called customui. This folder will contain the elements of your custom Ribbon tab. Within the folder, create a text file and call it customUI14.xml, as shown in Figure 26.3. Open the XML file in a text editor; either Notepad or WordPad will work.

Figure 26.3. Create a customuUI14.xml file within a customui folder.

Insert the basic structure for the XML code, shown here, into your XML file. For every opening tag grouping, such as <ribbon>, there must be a closing tag, </ribbon>:

startFromScratch is optional with a default value of false. It’s how you tell the code the other tabs in Excel will not be shown, only yours. True means to show only your tab; false means to show your tab and all the other tabs.

The <!-- your ribbon controls here --> you see in the previous code is commented text. Just enter your comments between <!-- and -->, and the program will ignore the line when it runs.

Creating the Tab and Group

Before you can add a control to a tab, you need to identify the tab and group. A tab can hold many different controls on it, which you can group together, like the Font group on the Home tab, as shown in Figure 26.4.

Figure 26.4. Individual controls are placed in groups on a tab. A tab may contain several such groups.

We’ll name our tab MrExcel Add-ins and add a group called Reports to it, as shown in Figure 26.5:

Figure 26.5. Add Tab and Group tags to your code to create a custom tab and group.

The id is a unique identifier for the control (in this case, the tab and group). The label is the text you want to appear on your ribbon for the specified control.

Adding a Control to Your Ribbon

After you’ve set up the ribbon and group, you can add controls. Depending on the type of control, there are different attributes you can include in your XML code. (Refer to Table 26.1 for more information on various controls and their attributes.)

Table 26.1. Ribbon Control Attributes

The following code adds a normal-sized button to the Reports group, set to run the sub called HelloWorld when the button is clicked (see Figure 26.6):

Figure 26.6. Run a program with a click of a button on your custom ribbon.

The id is a unique identifier for the control button. The label is the text you want to appear on your button. Size is the size of the button. normal is the default value, and the other option is large. onAction is the sub, HelloWorld, to call when the button is clicked. The sub, shown here, goes in a standard module, Module1, in the workbook:

Sub HelloWorld(control As IRibbonControl)
MsgBox "Hello World"
End Sub

Notice the argument control As IRibbonControl. This is the standard argument for a sub called by a button control using the onAction attribute. Refer to Table 26.2 for the required arguments for other attributes and controls.

Table 26.2. Control Arguments

Accessing the File Structure

The new Excel file types are actually zipped files containing various files and folders to create the workbook and worksheets you see when you open the workbook. To view this structure, rename the file, adding a .zip extension to the end of the filename. For example, if your filename is Chapter 26 – Simple Ribbon.xlsm, rename it to Chapter 26 – Simple Ribbon.xlsm.zip. You can then use your zip utility to access the folders and files within.

Copy into the zip file your customui folder and file, as shown in Figure 26.7. After placing them in the XLSM file, we need to let the rest of the Excel file know that they are there and what their purpose is. To do that, we modify the RELS file.

Figure 26.7. Using a zip utility, open the XLSM file and copy over the customui folder and file.

Understanding the RELS File

The RELS file, found in the _rels folder, contains the various relationships of the Excel file. Extract this file from the zip and open it using a text editor.

The file already contains existing relationships that we do not want to change. Instead, we need to add one for the customui folder. Scroll all the way to the right of the <Relationships line and place your cursor before the </Relationships> tag, as shown in Figure 26.8. Insert the following syntax:

Figure 26.8. Place your cursor in the correct spot for entering your custom ribbon relationship.

Id is any unique string to identify the relationship. If Excel has a problem with the string you enter, it may change it when you open the file. Target is the customui folder and file. Save your changes and add the RELS file back into the zip file.

Renaming the Excel File and Opening the Workbook

Rename the Excel file back to its original name by removing the .zip extension. Open your workbook.

Custom UI Editor Tool

It can be a little time-consuming to perform all the steps involved in adding a custom ribbon, especially if you make little mistakes and have to keep renaming your workbook, opening the zip file, extracting your file, modifying, adding it back to the zip, renaming, and testing. To aid in this, OpenXMLDeveloper.org offers the Custom UI Editor Tool, which you can learn more about at http://openxmldeveloper.org/articles/CustomUIeditor.aspx. It also updates the RELS file, helps with using custom images, and has other useful aids to customizing the ribbon.

Using Images on Buttons

The image that appears on a button can be either an image from the Microsoft Office icon library or a custom image you create and include within the workbook’s customui folder. With a good icon image, you can hide the button label but still have a friendly ribbon with images that are self-explanatory.

Microsoft Office Icons

Remember in legacy versions of Excel if you wanted to reuse an icon from an Excel button, you had to identify the faceid? It was a nightmare to do manually, though thankfully there were many tools out there to help you retrieve the information. Well, Microsoft must have heard the screams of agony because they’ve made it so much easier to reuse their icons. Not only that, instead of some meaningless number, they’ve provided easy-to-understand text!

Select File, Options, Customize Ribbon. Place your cursor over any menu command in the list, and a ScreenTip will appear, providing more information about the command. Included at the very end in parentheses is the image name, as shown in Figure 26.9.

Figure 26.9. Placing your cursor over a command, such as Insert Hyperlink, brings up the icon name, HyperlinkInsert.

To place an image on our button, we need to go back into the customUI14.xml file and advise Excel of what we want. The following code uses the HyperlinkInsert icon for the HelloWorld button and also hides the label, as shown in Figure 26.10. Note that the icon name is case sensitive:

Figure 26.10. You can apply the image from any Microsoft Office icon to your custom button.

Custom Icon Images

What if the icon library just doesn’t have the icon you’re looking for? You can create your own image file and modify the ribbon to use it:

1. Create a folder called images in the customui folder. Place your image in this folder.
2. Create a folder called _rels in the customui folder. Create a text file called customUI14.xml.rels in this new folder, as shown in Figure 26.11. Place the following code in the file. Note the Id for the image relationship is the name of the image file, mrexcellogo:

   

   

   Figure 26.11. Create a _rels and an images folder within the customui folder to hold files relevant to your custom image.

3. Open the customUI14.xml file and add the image attribute to the control, as shown here. Save and close the file:

   

4. Open the [Content_Types].xml file and add the following at the very end of the file but before the </Types>:

   <Default Extension="jpg" ContentType="application/octet-stream"/>
   

5. Save your changes, rename your folder, and open your workbook. The custom image appears on the button, as shown in Figure 26.12.
   

   Figure 26.12. With a few more changes to your customui, you can add a custom image to a button.

Troubleshooting Error Messages

To be able to see the error messages generated by a custom ribbon, go to File, Options, Advanced, General, and select the Show Add-in User Interface Errors option, as shown in Figure 26.14.

Figure 26.14. Select the Show Add-in User Interface Errors option to allow custom ribbon error messages to appear and aid you in troubleshooting.

The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema

As noted in the “Where to Add Your Code: customui Folder and File” section of this chapter, the case of the attributes is very particular. If an attribute is “mis-cased,” the error shown in Figure 26.15 may occur. The code in the customUI14.xml that generated the error had the following line:

<ribbon startfromscratch="false">

Instead of startFromScratch, the code contained startfromscratch (all lowercase letters). The error message even helps you narrow down the problem by naming the attribute with which it has a problem.

Figure 26.15. Mis-cased attributes can generate errors. Read the error message carefully; it might help you trace the problem.

Illegal Qualified Name Character

For every opening <, you need a closing >. If you forget a closing >, the error shown in Figure 26.16 may appear. The error message is not specific at all, but it does provide a line and column number where it’s having a problem. Still, it’s not the actual spot where the missing > would go. Instead, it’s the beginning of the next line. You’ll have to review your code to find the error, but you have an idea of where to start. The following code in the customUI14.xml generated the error:

Figure 26.16. For every opening <, you need a closing >.

Note the missing > for the group line (second line of code). The line should have been this:

<group id="CustomGroup" label="Reports">

Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”

If your structure is in the wrong order, such as the group tag placed before the tab tag as shown here, a chain of errors will appear, beginning with the one shown in Figure 26.17:

<group id="CustomGroup" label="Reports">
  <tab id="CustomTab" label="MrExcel Add-ins">

Figure 26.17. An error in one line can lead to string of error messages because the other lines are now considered out of order.

Excel Found Unreadable Content

Figure 26.18 shows a generic catchall message for different types of problems Excel can find. If you click Yes, you then receive the message shown in Figure 26.19. If you click No, the workbook doesn’t open. While creating ribbons, though, I found it appearing most often when Excel didn’t like the relationship id I had assigned the customui relationship in the RELS file. What’s nice is that if you click Yes, Excel will assign a new ID file, and the next time you open the file, the error should not appear.

Figure 26.18. This rather generic message could appear for many reasons. Click Yes to try to repair the file.

Original relationship:

Excel modified relationship:

In the RELS file, the error also appears if you split the relationship line within a quoted string. You may recall that you were cautioned against this in the “Understanding the RELS File” section, earlier in this chapter. In this case, Excel will not fix the file, and you must make the correction yourself.

Figure 26.19. Excel will let you know if it has succeeded in repairing the file.

Wrong Number of Arguments or Invalid Property Assignment

If there is a problem with the sub being called by your control, you might see the error in Figure 26.20 when you go to your ribbon. For example, the onAction of a button requires a single IRibbonControl argument such as the following:

Sub HelloWorld(control As IRibbonControl)

It would be incorrect to leave off the argument, as shown here:

Sub HelloWorld()

Figure 26.20. It’s important the subs being called by your controls have the proper arguments. Refer to Table 26.2 for the various control arguments.

Nothing Happens

If you open your modified workbook, and your ribbon doesn’t appear, but you don’t get any error messages, double-check your RELS file. It’s possible you forgot to update it with the required relationship to your custumUI14.xml.

Other Ways to Run a Macro

Custom ribbons are the best ways to run a macro; however, if you have only a couple of macros to run, it can be a bit of work to modify the file. You could have the client invoke a macro by going to the View tab, selecting Macros, View Macros, and then selecting the macro from the Macros dialog and clicking the Run button, but this is a bit unprofessional—and tedious. Other options are discussed in the following sections.

Keyboard Shortcut

The easiest way to run a macro is to assign a keyboard shortcut to a macro. From the Macro dialog box (Developer or View tab, click Macros, or press Alt+F8), select the macro and click Options. Assign a shortcut key to the macro. Figure 26.21 shows the shortcut Ctrl+Shift+C being assigned to the Clean1stCol macro. You can now conspicuously post a note on the worksheet reminding the client to press Ctrl+Shift+C to clean the first column.

Figure 26.21. The simplest way to enable a client to run a macro is to assign a shortcut key to the macro. Ctrl+Shift+C now runs the Clean1stCol macro.

Attach a Macro to a Command Button

Two types of buttons can be embedded in your sheet: the traditional button shape that can be found on the Forms control or an ActiveX command button. (Both can be accessed on the Developer tab under the Insert option.)

To add a Forms control button with a macro to your sheet, follow these steps:

1. On the Developer tab, click the Insert button and select the button control from the Forms section of the drop-down, as shown in Figure 26.22.
   

   Figure 26.22. The Forms controls are found under the Insert icon on the Developers tab.

2. Place your cursor in the worksheet where you want to insert the button, and then click and drag to create the shape of your new button.
3. When you release the mouse button, the Assign Macro dialog displays. Select a macro to assign to the button and select OK.
4. Highlight the text on the button and type new meaningful text.
5. To change the font, text aligment and other aspects of the button’s appearance, right-click the button and select Format Control from the pop-up menu.
6. To reassign a new macro to the button, right-click the button and select Assign Macro from the pop-up menu.

Attach a Macro to a Shape

The previous method assigned a macro to an object that looks like a button. You can also assign a macro to any drawing object on the worksheet. To assign a macro to an Autoshape, right-click the shape and select Assign Macro, as shown in Figure 26.23.

Figure 26.23. Macros can be assigned to any drawing object on the worksheet.

I prefer this method because I can easily add a drawing object with macro code and use the OnAction property to assign a macro to the object. There is one big drawback to this method: If you assign a macro that exists in another workbook, and the other workbook is saved and closed, Excel changes the OnAction for the object to be hard-coded to a specific folder.

Attach a Macro to an ActiveX Control

ActiveX controls are newer than Form controls and slightly more complicated to set up. Instead of simply assigning a macro to the button, you will have a button_click procedure where you can either call another macro or have the macro code actually embedded in the button_click procedure. Follow these steps:

1. On the Developer tab, click the Insert button and select the Command Button icon from the ActiveX Controls section of the drop-down Control toolbox.
2. Draw a button shape on the worksheet as described in step 2 for the Forms button.
3. To format the button, right-click the button and select Properties or select Properties from the Developer tab. You can now adjust the button’s caption and color in the Properties window, as shown in Figure 26.24. If nothing happens when you right-click the button, enter Design mode by clicking the Design Mode button on the Developer tab.
   

   Figure 26.24. Clicking the Properties icon brings up the Properties window, where you can adjust many aspects of the ActiveX button.

   ---

   Note

   There is one annoying aspect of this Properties window: It is huge and covers a large portion of your worksheet. Eventually, if you want to use the worksheet, you are going to have to close this Properties window. When you close the Properties window, it also hides the Properties window in the VB Editor. I would prefer that I could close this Properties window without affecting my VB Editor environment.

   ---

4. To assign a macro to the button, click the View Code button on the Controls group of the Developer tab. This creates a new procedure on the code pane for the current worksheet. Type the code that you want to have run or the name of the macro you want to run in this procedure. Figure 26.25 shows the code for the button. This code appears on the code pane for the worksheet.
   

   Figure 26.25. Click the View Code button in the Control Toolbox toolbar to open the macro for this button.

Running a Macro from a Hyperlink

Using a trick, it is possible to run a macro from a hyperlink. Because many people are used to clicking a hyperlink to perform an action, this method might be more intuitive for your clients.

The trick is to set up placeholder hyperlinks that simply link back to themselves. Select a cell and from the Insert tab, select Hyperlink (or press Ctrl+K). In the Insert Hyperlink dialog, click Place in This Document. Figure 26.26 shows a worksheet with four hyperlinks. Each hyperlink points back to its own cell.

Figure 26.26. To run a macro from a hyperlink, you must create placeholder hyperlinks that link back to their cells. Then, using an event handler macro on the worksheet’s code pane, you can intercept the hyperlink and run any macro.

When a client clicks a hyperlink, you can intercept this action and run any macro by using the FollowHyperlink event. Enter the following code on the code module for the worksheet:

Next Steps

From custom ribbons to simple buttons or hyperlinks, there are plenty of ways to ensure your clients never need to see the Macro dialog box. In Chapter 27, “Creating Add-Ins,” you learn how to package your macros into add-ins that can be easily distributed to others.