One of the first changes you notice when you open Excel 2007 is the new ribbon toolbar. Gone are the menus and toolbars of old. And this change isn’t just visual—the method of modifying custom menu controls has changed just as radically. One of the biggest bonuses of this new method—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 Command Bars object still works, but the customized menus and toolbars are all placed on the Add-ins ribbon. 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 Office menu and select Excel 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. See the “Troubleshooting Error Messages” section later in this chapter for more details.
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 customui.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>:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/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.
Caution
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 ribbon, 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:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/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>
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.)
Table 26.1. Ribbon Control Attributes
Attribute | Type or Value | Description |
|---|---|---|
| String | Specifies description text displayed in menus when the |
|
| Specifies whether the control is enabled |
| Callback | Retrieves XML content that describes a dynamic menu |
| Callback | Gets the description of a control |
| Callback | Gets the enabled state of a control |
| Callback | Gets the image for a control |
| Callback | Gets a built-in control’s icon by using the control ID |
| Callback | Gets the number of items to be displayed in a combo box, drop-down list, or gallery |
| Callback | Gets the ID for a specific item in a combo box, drop-down list, or gallery |
| Callback | Gets the image of a combo box, drop-down list, or gallery |
| Callback | Gets the label of a combo box, drop-down list, or gallery |
| Callback | Gets the ScreenTip for a a combo box, drop-down list, or gallery |
| Callback | Gets the Enhanced ScreenTip for a combo box, drop-down list, or gallery |
| Callback | Gets the KeyTip for a control |
| Callback | Gets the label for a control |
| Callback | Gets a value that indicates whether a toggle button is pressed or not pressed |
Gets a value that indicates whether a check box is selected or cleared | ||
| Callback | Gets the ScreenTip for a control |
| Callback | Gets the ID of the selected item in a drop-down list or gallery |
| Callback | Gets the index of the selected item in a drop-down list or gallery |
| Callback | Gets a value specifying whether to display the control image |
| Callback | Gets a value specifying whether to display the control label |
| Callback | Gets a value specifying the size of a control (normal or large) |
| Callback | Gets a value specifying the Enhanced ScreenTip for a control |
| Callback | Gets the text to be displayed in the edit portion of a text box or edit box |
| Callback | Gets the text to be displayed (rather than a horizontal line) for a menu separator |
| Callback | Gets a value that specifies whether the control is visible |
| String | A user-defined unique identifier for the control (mutually exclusive with |
| Control id | Built-in control ID (mutually exclusive with |
| Qualified id | Qualified control ID, prefixed with a namespace identifier (mutually exclusive with |
| String | Specifies an image for the control |
| Control id | Specifies an identifier for a built-in image |
| Control id | Specifies the identifier for the built-in control after which to position this control |
| Qualified id | Specifies the identifier of a a control whose |
| Control id | Specifies the identifier for the built-in control before which to position this control |
| Qualified id | Specifies the identifier of a control whose |
|
| Specifies the size for the items in a menu |
| String | Specifies the KeyTip for the control |
| String | Specifies the label for the control |
| Callback | Called when the user clicks the control |
| Callback | Called when the user enters or selects text in an edit box or combo box |
| String | Specifies the control’s ScreenTip |
|
| Specifies whether the control’s image is shown |
|
| Specifies whether to show the image in a combo box, drop-down list, or gallery |
|
| Specifies whether to show the label in a combo box, drop-down list, or gallery |
|
| Specifies whether the control’s label is shown |
|
| Specifies the size for the control |
| String | Indicates the width for the control by specifying a string, such as |
| String | Specifies the Enhanced ScreenTip for the control |
| String | Specifies user-defined text |
| String | Specifies the text to be displayed, rather than a horizontal line, for a menu separator |
|
| Specifies whether the control is visible |
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):
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/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>
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
Control | Callback Name | Signature |
|---|---|---|
Various 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:
<Relationship Id="rAB67989" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customui/customui.xml"
Caution
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/ customui.xml"
Note that Excel will merge the three separate lines above 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. (See the troubleshooting section “Excel Found Unreadable Content” later in this chapter for more information.) 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. Refer to the “Troubleshooting Error Messages” section in this chapter if any error messages appear.
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, Patrick Schmid of pschmid.net has created the RibbonCustomizer, which does most of these actions for you within its interface. Go to http://pschmid.net/office2007/ribboncustomizer/index.php for more information about this tool.
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 earlier 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!
Choose Office menu, Excel Options, Customize. 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 customui 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:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/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>
Tip
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 here: 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:
Create a folder called
imagesin the customui folder. Place your image in this folder.Create a folder called
_relsin the customui folder. Create a text file calledcustomui.xml.relsin this new folder, as shown in Figure 26.11. Place the following code in the file. Note theIdfor 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>
Open the customui.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/2006/01/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>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"/>
Save your changes, rename your folder, and open your workbook. The custom image appears on the button, as shown in Figure 26.12.
Converting an Excel 2003 Custom Toolbar to Excel 2007
You have a workbook and custom toolbar designed in Excel 2003 with several buttons. You’re now ready to transfer over to Excel 2007. When you open the workbook in 2007, the toolbar doesn’t appear on the Add-ins ribbon because the toolbar wasn’t designed with VBA; it is a manually created custom toolbar.
After saving the workbook as an XLSM file, create the customui file, as shown here. The tab is called My Quick Macros, and it has two groups: Viewing Options and Shortcuts:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="customMacros" label="My Quick Macros">
<group id="customview" label="Viewing Options">
<button id="btn_r1c1" label="Toggle R1c1"
onAction="mod_2007.myButtons" />
<button id="btn_Headings" label="Show Headings."
onAction="mod_2007.myButtons" imageMso = "TableStyleClear"/>
<button id="btn_gridlines" label="Show Gridlines"
onAction="mod_2007.myButtons" imageMso = "BordersAll"/>
<button id="btn_tabs" label="Show Tabs"
onAction="mod_2007.myButtons" imageMso = "Connections"/>
</group>
<group id="customshortcuts" label="Shortcuts">
<button id="btn_formulas" label="Highlight Formulas"
onAction="mod_2007.myButtons" imageMso = "FunctionWizard"/>
</group>
</tab>
</tabs>
</ribbon>After updating the RELS file (see the section “Understanding the RELS File” earlier in this chapter for more details), open the workbook to see the new tab, as shown in Figure 26.13.
Now it’s time to update the code in the workbook. You’ll notice the onAction in the customui folder all pointed to the same sub, mod_2007.myButtons, instead of each having a custom call. Because all the controls are of the same type, buttons, and have the same argument type, iRibbonControl, we can take advantage of these facts. Create a single sub, myButtons, in a module called mod_2007 to handle all the button calls using Select Case to manage the IDs of each button:
Sub myButtons(control As IRibbonControl)
Select Case control.ID
Case Is = "btn_r1c1"
SwitchR1C1
Case Is = "btn_Headings"
ShowHeaders
Case Is = "btn_gridlines"
ShowGridlines
Case Is = "btn_tabs"
ShowTabs
Case Is = "btn_formulas"
GoToFormulas
End Select
End SubThe control.IDs are the ids assigned each button in the customui.xml file. The action within each Case statement is a call to the desired sub. Here is a sample of one of the subs being called, ShowHeaders. It is the same sub that was in the original 2003 workbook:
Sub ShowHeaders()
If ActiveWindow.DisplayHeadings = False Then
ActiveWindow.DisplayHeadings = True
Else
ActiveWindow.DisplayHeadings = False
End If
End Sub
To be able to see the error messages generated by a custom ribbon, go to the Office menu and select Excel Options, Advanced, General, and check Show Add-in User Interface Errors, as shown in Figure 26.14.
As noted in the section “Where to Add Your Code: customui Folder and File” 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 customui.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 it’s having a problem with.
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 customui.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">
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:
<Relationship Id="rId3" Type=http://schemas.microsoft.com/office/2006/relationships/ui/extensibility Target="customui/customui.xml"/>
Excel modified relationship:
<Relationship Id="rE1FA1CF0-6CA9-499E-9217-90BF2D86492F" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customui/customui.xml"/>
The error also appears if, in the RELS file, you split the relationship line within a quoted string, as cautioned against in the section “Understanding the RELS File” 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()
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.
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. In Excel 2003, it wasn’t a big deal to have a client invoke a macro by going to Tools, Macro, Macros, selecting the macro from the Macro dialog, and clicking the Run button (although it was a bit unprofessional). However, that option is far from convenient now.
The easiest way to run a macro is to assign a keyboard shortcut to a macro. From the Macro dialog box (Developer ribbon, 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.
Caution
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, Q, and T are usually good choices because as of Excel 2007, they have not yet been assigned to Excel’s menu of “Ctrl+” shortcut combinations. Ctrl+L used to be available, but this is now used to create a table in Excel 2007.
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 ribbon, under the Insert option).
To add a Forms control button with a macro to your sheet, follow these steps:
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.
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.
When you release the mouse button, the Assign Macro dialog displays. Select a macro to assign to the button and choose OK.
Highlight the text on the button and type new meaningful text.
To change the color, font, and other aspects of the button’s appearance, right-click the button and choose Format Control from the pop-up menu.
Caution
There are two versions of the Format Control dialog box. One version offers seven tabs with settings for Font, Alignment, Size, Protection, Properties, Margins, and Web. The second version offers only one tab with settings for Font.
Which version you see is based on something very subtle. When you right-click the control, the look of the selection border can be either diagonal lines, as shown in Figure 26.23, or dots, as shown in Figure 26.24. The diagonal lines selection border tends to appear immediately after editing the text on the button and leads to the Font-only Format Control dialog. The dots selection border leads to the full-featured Format Control dialog.
If you right-click and diagonal lines appear as the selection border, you should first left-click the diagonal line selection border to change it to dots. You can then right-click again and choose Format Control.
To reassign a new macro to the button, right-click the button and choose Assign Macro from the pop-up menu.
This 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.25.
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:
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.
Draw a button shape on the worksheet as described in step 2 for the Forms button.
To format the button, right-click the button and select Properties or select Properties from the Developer ribbon. You can now adjust the button’s caption and color in the Properties window, as shown in Figure 26.26. If nothing happens when you right-click the button, enter Design mode by clicking the Design Mode button on the Developer ribbon.
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. You can see that this is missing in Figure 26.27. I would prefer that I could close this Properties window without affecting my VB Editor environment.
To assign a macro to the button, click the View Code button on the Controls group of the Developer ribbon. 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.27 shows the code for the button. This code appears on the code pane for the worksheet.
Using a trick, it is possible to run a macro from a hyperlink. Because many clients 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 ribbon, select Hyperlink, and press Ctrl+K. In the Insert Hyperlink dialog, click Place in This Document. Figure 26.28 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:
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 SubFrom 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 will learn how to package your macros into add-ins that can be easily distributed to others.