25. 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 2013 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. One thing to keep in mind is that with the change to a single document interface (SDI), the custom ribbon tab attached to a workbook is visible only when that workbook is active. When you activate another workbook, the tab will not appear on the ribbon. The exception is with an add-in; its custom ribbon is visible on any workbook open after the add-in is opened.

→ See Chapter 28, “What’s New in Excel 2013 and What’s Changed,” for more information on SDI.

→ See Chapter 26, “Creating Add-Ins,” for more information on creating an add-in.

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 25.1. In Figure 25.2, the custom toolbars from two different workbooks appear together on the Custom Toolbars group.

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

Figure 25.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 beginning, go to the File tab and select Options, Advanced, General, and select Show Add-In User Interface Errors. This allows error messages to appear so that you can troubleshoot errors in your custom toolbar.

→ See the “Troubleshooting Error Messages” section, p. 548, for more details.

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 25.3. Open the XML file in a text editor; either Notepad or WordPad will work.

Figure 25.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>:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>

      <!-- your ribbon controls here -->

    </tabs>
  </ribbon>
</customUI>

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

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

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="CustomTab" label="MrExcel Add-ins">
        <group id="CustomGroup" label="Reports">

          <!-- your ribbon controls here -->

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Figure 25.4. Add Tab and Group tags to your code to create a custom tab, MrExcel Add-Ins, and group, Reports.

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 25.1 for more information on various controls and their attributes.)

Table 25.1. Ribbon Control Attributes

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

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
       <tab id="CustomTab" label="MrExcel Add-ins">
         <group id="CustomGroup" label="Reports">

           <button id="button1" label="Click to run"
               onAction="Module1.HelloWorld" size="normal"/>

         </group>
        </tab>
    </tabs>
  </ribbon>
</customUI>

Figure 25.5. 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 25.2 for the required arguments for other attributes and controls.

Table 25.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 25 – Simple Ribbon.xlsm, rename it to Chapter 25 – 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 25.6. After placing them in the XLSM file, you need to let the rest of the Excel file know that they are there and what their purpose is. To do that, modify the RELS file.

Figure 25.6. 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 file and open it using a text editor.

The file already contains existing relationships that you do not want to change. Instead, you 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 25.7. Insert the following code:

<Relationship Id="rAB67989"
Type="http://schemas.microsoft.com/office/2007/relationships/ui/_
extensibility"
Target="customui/customUI14.xml"/>

Figure 25.7. 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 might 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.

→ See the troubleshooting section “Excel Found a Problem with Some Content,” p. 549, for more information.

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.

→ If any error messages appear when you rename an Excel file, see “Troubleshooting Error Messages,” p. 548.

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/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx. The tool 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.

Using Microsoft Office Icons on Your Ribbon

Remember how, 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, but instead of using 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 25.8.

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

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

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="CustomTab" label="MrExcel Add-ins">
        <group id="CustomGroup" label="Reports">

          <button id="button1" label="Click to run"
            onAction="Module1.HelloWorld" imageMso="HyperlinkInsert"
            showLabel = "false" />

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

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

You aren’t limited to just the icons available in Excel. You can use the icon for any installed Microsoft Office application. You can download a workbook from Microsoft with several galleries showing the icons available (and their names) from http://www.microsoft.com/en-us/download/details.aspx?id=11675.

Adding Custom Icon Images to Your Ribbon

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 25.10. Place the following code in the file. Note that the Id for the image relationship is the name of the image file, mrexcellogo:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/_
relationships"><Relationship Id="mrexcellogo"_
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/_
image"
Target="images/mrexcellogo.jpg"/></Relationships>

Figure 25.10. Create a _rels folder 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.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="CustomTab" label="MrExcel Add-ins">
        <group id="CustomGroup" label="Reports">

          <button id="button1" label="Click to run"
            onAction="Module1.HelloWorld" image="mrexcellogo"
            size="large" />

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

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

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

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 25.13 might occur.

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

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.

Illegal Qualified Name Character

For every opening <, you need a closing >. If you forget a closing >, the error shown in Figure 25.14 might appear. The error message is not specific at all, but it does provide a line and column number to indicate 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 have to review your code to find the error, but you have an idea of where to start.

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

The following code in the customUI14.xml generated the error:

<tab id="CustomTab" label="MrExcel Add-ins">
  <group id="CustomGroup" label="Reports"
<button id="button1" label="Click to run"
  onAction="Module1.HelloWorld" image="mrexcellogo"
  size="large" />

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

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

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

Excel Found a Problem with Some Content

Figure 25.16 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 25.17. 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 25.16. This rather generic message could appear for many reasons. Click Yes to try to repair the file.

Figure 25.17. Excel lets you know whether it has succeeded in repairing the file.

Original relationship:

<Relationship Id="rId3"
Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility"
Target="customui/customUI14.xml"/>

Excel modified relationship:

<Relationship Id="rE1FA1CF0-6CA9-499E-9217-90BF2D86492F"
Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility"
 Target="customui/customuUI14.xml"/>

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

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 25.18 when you try to run code from your ribbon. For example, the onAction of a button requires a single IRibbonControl argument such as the following:

Sub HelloWorld(control As IRibbonControl)

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

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

Sub HelloWorld()

Invalid File Format or File Extension

This error message in Figure 25.19 looks rather drastic, but it could be deceiving. You could get it if you’re missing quotation marks around an attribute’s value in the RELS file. For example, look carefully at the following line to see that the Type value is missing its quotations marks:

Type=http://schemas.microsoft.com/office/2007/relationships/ui/extensibility

Figure 25.19. A missing quotation mark can generate a drastic message, but it’s easily fixed.

The line should have been this:

Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility"

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.

Using a Keyboard Shortcut to Run a Macro

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

Attaching 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 and an ActiveX command button. (Both can be accessed on the Developer tab under the Controls, 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 25.21.

Figure 25.21. The Forms controls are found under the Insert icon on the Developer 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 click OK.

4. Highlight the text on the button and type new meaningful text.

5. To change the font, text alignment, 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.

Attaching 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, as shown in Figure 25.22. To assign a macro to an Autoshape (Insert, Illustrations, Shapes), right-click the shape and select Assign Macro.

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

This method is useful because you can easily add a drawing object with code and use the OnAction property to assign another 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.

Attaching 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 event where you can either call another macro or have the macro code actually embedded in the event. Follow these steps:

1. On the Developer tab, click the Insert button and select the Command Button icon from the ActiveX Controls section.

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. To format the button, right-click the button and select Properties or select Controls, Properties from the Developer tab. You can now adjust the button’s caption and color in the Properties window, as shown in Figure 25.23. If nothing happens when you right-click the button, enter Design mode by clicking the Design Mode button on the Developer tab.

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

4. To assign a macro to the button, right-click it and select View Code. This creates the header and footer for the button_click event in 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 call.

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 the cell with the text you want to link to, and from the Insert tab, select Links, Hyperlink (or press Ctrl+K). In the Insert Hyperlink dialog, click Place in This Document. Figure 25.24 shows a worksheet with four hyperlinks. Each hyperlink points back to its own cell.

Figure 25.24. 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:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.TextToDisplay
    Case "Widgets"
        RunWidgetReport
    Case "Gadgets"
        RunGadgetReport
    Case "Gizmos"
        RunGizmoReport
    Case "Doodads"
        RunDooDadReport
End Select
End Sub

Next Steps

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