IN THIS CHAPTER
Out with the Old, In with the New
Adding a Control to Your Ribbon
Renaming the Excel File and Opening the Workbook
Troubleshooting Error Messages
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.
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.
Unlike programming in the VB Editor, you won’t have any assistance with automatic correction of letter case, and the XML code, which is what the ribbon code is, is very particular. Note the case of the XML-specific words, such as id—using ID will generate an error.
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.
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.
Note the case of the letters in startFromScratch
—the small s at the beginning followed by the capital F in From
and capital S in Scratch
. It is crucial you do not deviate from this.
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.
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.
We’ll name our tab MrExcel Add-ins and add a group called Reports to it, as shown in Figure 26.5:
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.
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.)
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):
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.
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.
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:
Even though the previous code appears as three lines in this book, it should appear as a single line in the RELS file. If you want to enter it as three separate lines, do not separate the lines within the quoted strings. The preceding examples are correct breaks. An incorrect break of the third line, for example, would be this:
Target = "customui/
customUI14.xml"
Note that Excel will merge these three separate lines into one, when the workbook is opened.
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.
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. 577.
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.
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.
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.
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:
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/downloads/details.aspx?familyid=12b99325-93e8-4ed4-8385-74d0f7661318.
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:
Id
for the image relationship is the name of the image file, mrexcellogo:
</Types>
:
<Default Extension="jpg" ContentType="application/octet-stream"/>
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.
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.
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:
Note the missing >
for the group line (second line of code). The line should have been this:
<group id="CustomGroup" label="Reports">
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.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.
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.
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()
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
.
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.
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.
Be careful when assigning keyboard shortcuts. Many of the keys are already mapped to important Windows shortcuts. If you would happen to assign a macro to Ctrl+C, anyone who uses this shortcut to copy the selection to the clipboard will be frustrated when your application does something else in response to this common shortcut. Letters E, J, M, and Q are usually good choices because as of Excel 2010, they have not yet been assigned to Excel’s menu of “Ctrl+” shortcut combinations. Ctrl+L and Ctrl+T used to be available, but these are now used to create a table in Excel 2010.
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:
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.
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.
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:
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.
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.
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:
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.