Using Microsoft Office icons on a ribbon

Microsoft has made it fairly easy to reuse Microsoft’s button images in custom ribbons. Select File, Options, Customize Ribbon. Place your mouse pointer over any menu command in the list, and a screentip displays, providing more information about the command. Included at the very end, in parentheses, is the image name, as shown in Figure 25-5.

To place an image on your button, you need to go back into the customUI14.xml file and tell Excel what you want. The following code uses the HyperlinkInsert icon for the HelloWorld button and makes it large, as shown in Figure 25-6. (Note that the icon name is case sensitive.)

Click here to view code image

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon startFromScratch="false">
   <tabs>
    <tab id="CustomTab" label="My First Ribbon">
      <group id="CustomGroup" label="My Programs">
 
        <button id="button1" label="Click to run"
          onAction="Module1.HelloWorld"
          imageMso="HyperlinkInsert" size="large"/>
 
     </group>
    </tab>
   </tabs>
 </ribbon>
</customUI>

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 Word document from Microsoft with two galleries showing the icons available (and their names) from http://www.microsoft.com/en-us/download/details.aspx?id=21103.

Adding custom icon images to a 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. Follow these steps:

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-7. Place the following code in the file (and note that the Id for the image relationship is the name of the image file, helloworld_png):

   Click here to view code image

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

   

   Open the customUI14.xml file and add the image attribute to the control, as shown here, before you save and close the file:

   Click here to view code image

   <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
       <tabs>
        <tab id="CustomTab" label="My First Ribbon">
          <group id="CustomGroup" label="My Programs">
    
            <button id="button1" label="Click to run"
              onAction="Module1.HelloWorld" image="helloworld"
              size="large"/>
    
        </group>
       </tab>
      </tabs>
    </ribbon>
   </customUI>

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

   Click here to view code image

   < Default Extension="png" ContentType="image/.png"/>

   Note

   If your image is a jpg, you would use the following:

   Click here to view code image

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

4. Save your changes, rename your folder, and open your workbook. The custom image appears on the button, as shown in Figure 25-8.

   

The attribute “Attribute Name” on the element “customui ribbon” is not defined in the DTD/schema

As noted in the section “Where to add code: The customui folder and file” earlier in this chapter, the case of attributes is very particular. If an attribute is “mis-cased,” the error shown in Figure 25-9 might occur.

The code in the customUI14.xml file that generated the error had the following line:

Click here to view code image

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

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

Click here to view code image

<tab id="CustomTab" label="My First Ribbon">
 <group id="CustomGroup" label="My Programs"
   <button id="button1" label="Click to run"
    onAction="Module1.HelloWorld" image="helloworld_png"
    size="large"/>

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

Click here to view code image

<group id="CustomGroup" label="My Programs">

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 appears, beginning with the one shown in Figure 25-11.

Click here to view code image

<group id="CustomGroup" label="My Programs">
  <tab id="CustomTab" label="My First Ribbon">

Found a problem with some content

Figure 25-12 shows a generic catchall message for different types of problems Excel can find. If you click No, the workbook doesn’t open. If you click Yes, you then receive the message shown in Figure 25-13. While creating ribbons, though, I found it appearing most often when Excel doesn’t like the Relationship ID I have assigned to the customui relationship in the RELS file. What’s nice is that if you click Yes in the “Found a Problem” dialog box, Excel assigns a new ID, and the next time you open the file, the error should not appear.

Here’s the original relationship:

Click here to view code image

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

Here’s the Excel-modified relationship:

Click here to view code image

<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 a control, you might see the error message in Figure 25-14 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:

Click here to view code image

Sub HelloWorld(control As IRibbonControl)

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

Sub HelloWorld()

Invalid file format or file extension

The error message shown in Figure 25-15 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, and you’ll see that the Type value is missing its quotation marks:

Click here to view code image

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

The line should have been this:

Click here to view code image

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 an error message, double-check your RELS file. It’s possible that you forgot to update it with the required relationship to your customUI14.xml file.

Using a keyboard shortcut to run a macro

The easiest way to run a macro is to assign a keyboard shortcut to it. Open the Macro dialog box by selecting the Developer or View tab and clicking Macros or by pressing Alt+F8. Then select the macro and click Options. Assign a shortcut key to the macro. Figure 25-16 shows the shortcut Ctrl+Shift+H being assigned to the RunHello macro. You can now conspicuously post a note on the worksheet, reminding the client to press Ctrl+Shift+H to clean the first column.

Attaching a macro to a command button

Two types of buttons can be embedded in a sheet: the traditional button shape that you can find in the Form Controls section and an ActiveX command button. (You can access both on the Developer tab under the Controls, Insert option.)

To add a form 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 Form Controls section of the drop-down, as shown in Figure 25-17.

   Place your cursor in the worksheet where you want to insert the button and then click and drag to create the shape of the new button. When you release the mouse button, the Assign Macro dialog box displays.

2. In the Assign Macro dialog box, select a macro to assign to the button and click OK.

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

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

   

5. 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. To assign a macro to an Autoshape (which you get by selecting Insert, Illustrations, Shapes), right-click the shape and select Assign Macro, as shown in Figure 25-18.

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 a button, you 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 the 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-19. If nothing happens when you right-click the button, enter Design mode by clicking the Design Mode button on the Developer tab.

   

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 window for the current worksheet. Type the code you want to have run or the name of the macro you want to call.

Running a macro from a hyperlink

There is a trick you can use to run a macro from a hyperlink. Because many people are used to clicking a hyperlink to perform an action, this method might be the most 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, Link, Insert Link (or press Ctrl+K). In the Insert Hyperlink dialog, click Place In This Document. Figure 25-20 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 in the code module for the worksheet:

Click here to view code image

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.TextToDisplay
  Case "Quarter 1"
    RunQuarter1Report
  Case "Quarter 2"
    RunQuarter2Report
  Case "Quarter 3"
    RunQuarter3Report
  Case "Quarter 4"
    RunQuarter4Report
End Select
End Sub