Chapter 31. Programming the Office 2010 Ribbon

In Office 2007, the Ribbon could not be customized by the user via the Office 2007 user interface. This capability has been added to Office 2010. The user can now click the File tab on the Ribbon, choose Options, then click Customize Ribbon.

VBA programmers, however, may want to customize the Ribbon programmatically (by running code rather than working directly with the Options dialog box). Perhaps your organization wants to hide certain features in Excel, add a step-through wizard to Word, create a Ribbon that is custom designed for working with a particular presentation, add a special tab containing capabilities relevant to your business, or otherwise automate management of this major part of the user interface. Or you might want to create dynamic Ribbon effects, such as hiding, disabling, revealing, or modifying Ribbon elements—labels, groups, controls, or whatever—as appropriate, based on the user's behaviors in the application or some other criterion.

This chapter explores all aspects of Ribbon customization so you'll be able to fully exploit the Ribbon's capabilities programmatically.

Note that the Ribbon can be programmatically modified in two ways: The most efficient approach is to create XML code and make it interact with VBA procedures. This chapter employs this technique and describes how to customize the Ribbon in Word, Excel, and PowerPoint. A second, more complex approach requires writing COM add-ins, a technique that is beyond the scope of this book.

The Access Ribbon can't be modified in the same way that you modify the Ribbon in Word, Excel, and PowerPoint. Access requires a unique approach, including creating a specialized table to hold the XML code that modifies the Ribbon. Modifying the Access Ribbon is covered at the end of this chapter.

The Ribbon's contents are described in the XML language, but you don't need to know how to write XML to manipulate the Ribbon. Throughout this chapter, you can just copy and paste XML code examples, making a few modifications to them to suit your needs.

As you'll see shortly, there's also a handy utility you can download that helps you avoid several tedious steps when modifying the Ribbon and also verifies that your XML statements are "well formed" (that they follow the rules of XML and thus should work).

In this chapter you will learn to do the following:

  • Hide a group on the Ribbon

  • Add a new, custom group

  • Create callbacks for event handling

  • Manipulate the Access Ribbon

  • Debug Ribbon programming

Hiding the Editing Group on the Word Ribbon

To get an idea of how to modify the Ribbon, let's assume that you want to remove the Editing group in the Word Ribbon's Home tab. This group has three options: Find, Replace, and Select. However, you decide that you just don't need to display these options because you always press Ctrl+F to open the Find dialog box and Ctrl+H to open the Replace dialog box, and you select by dragging the mouse.

To hide the Editing group on the Ribbon, follow these steps:

  1. First you'll want to download a free utility that makes working with the Ribbon much easier. Go to http://openxmldeveloper.org/articles/CustomUIeditor.aspx and download, then install, the Office Custom UI Editor tool. This utility can be found at the bottom of the article on this web page, following the word Attachment(s). Download OfficeCustomUIEditorSetup.zip. The file is just above the comments.

  2. Start Word.

  3. Press Alt+F, N to create a new blank document. (Or click the File tab, then click the New option.)

  4. Press Alt+F, A and save the document as RibbonMod.docm to your desktop (or another location where you can easily locate it).

  5. Press Alt+F, C to close this document. Closing the document is necessary because if it's still open when you attempt to store your XML code in it (by choosing File

    Hiding the Editing Group on the Word Ribbon
  6. Run the Custom UI Editor for Microsoft Office.

  7. Choose File

    Why You Should Use Macro-Enabled File Types
  8. Browse to the RibbonMod.docm file that you saved in step 4, and open it.

  9. In the right pane of the Custom UI Editor, type the following XML code:

    /2009/07/<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
      <ribbon>
         <tabs>
    <tab idMso="TabHome">
               <group idMso="GroupEditing" visible="false" />
            </tab>
         </tabs>
      </ribbon>
    </customUI>

    Identifiers (idMso), images (imageMso), and other attributes in Ribbon XML code can have an Mso suffix. Mso is short for Microsoft Office, and when appended to an attribute it means built-in. So, a tab with an idMso attribute is one of the tabs on the Ribbon by default. A tab with a plain id attribute is a new tab you've added to the Ribbon. Likewise, an imageMso is one of the set of built-in Office 2010 icons, but an image is an icon you created by importing a graphics file (see "Creating Your Own Icons" later in this chapter).

    Note that if you are copying this code from a web page or a Word file (or some other context that adds various formatting codes to text), it's a good idea to paste the code into Notepad first. Next, select and copy it from Notepad, and then paste it into the UI Editor. Notepad strips off most formatting, so you won't mess up the XML with extraneous, nonprinting characters.

  10. Click the icon with the red check mark.

    This validates your XML code (a very handy feature). If you don't now see the message "Custom UI XML is well formed," you've made a typo in the XML code. Retype it (or better yet, copy and paste it from this book's web page—see this book's introduction for information on copying code). You should always validate your XML code because if there is an error of some kind, your Ribbon customization simply won't happen. But you will be given no error message or other warning. It just won't work.

  11. Choose File

    Why You Should Use Macro-Enabled File Types
  12. Now, to see the effect, open the document by clicking the File tab on Word's Ribbon and then clicking the Recent option and choosing RibbonMod.docm from the Recent Files list (or double-click the filename in Windows Explorer).

If you entered the correct XML code, you'll see a Ribbon like the one on the bottom of Figure 31.1.

Word's Ribbon with (above) and without its Editing group (below)

Figure 31.1. Word's Ribbon with (above) and without its Editing group (below)

The key lines in the XML code are these:

<tab idMso="TabHome">
                    <group idMso="GroupEditing" visible="false" />

The line of code that begins with <tab specifies the tab on the Ribbon you want to modify—in this case, it's the Home tab. The group element specifies which group within the tab you are targeting. In this case, it's the Editing group. Finally, the code specifies that the Editing group's Visible attribute should be set to false. (I'll define the XML terms element and attribute shortly.)

A Word of Warning

One major problem you must try to avoid when working with XML is that when XML was being designed, some committee decided that this language would be case sensitive. That decision has caused countless problems for programmers. It's easy to generate bugs in languages that are case sensitive because ThisVariableName is not the same as ThisVariablename. The capital N in the first version means that these two words refer to two different variables (even though, of course, the programmer intended them to represent the same variable). And since the two versions look very much the same, this kind of bug can be difficult to locate and fix. Your XML code won't work if you don't precisely match capitalization.

Fortunately and sensibly, VBA isn't case sensitive, so you never have to worry about this type of error in VBA code. But XML is case sensitive, so if you're getting an error when attempting to modify the Ribbon, make sure you're not having a case mismatch. And a good rule is to simply always use lowercase when writing XML code. If you name a function or variable without any capitalization—and stick to that practice—you'll avoid the case-related debugging headaches that XML programmers have struggled with for years.

See the section at the end of this chapter titled "What to Look For If Things Go Wrong" for additional possible problems when working with the Ribbon.

XML Terminology

To understand the descriptions of XML code examples in this chapter, you need to know the meaning of two terms.

In XML, an element is roughly what a programmer would call an object. For example, a button control is an element in XML code. An element's tag (name) is enclosed within these symbols: < and /> (immediately following the < is the name of the element).

And just as objects in ordinary programming (such as VBA) have properties and methods, XML's elements have attributes. So, in the following example code, a button element is defined, and it has four attributes: label, size, onAction, and imageMso. Of the four, three are analogous to properties, with onAction similar to an object's method. But in XML, they are all simply referred to as attributes.

<button id="b1"
       label="Check Spelling"
       size="large"
       onAction="module1.test"
       imageMso="diamond" />

Because each attribute is named (label, size, and so on), the order of the attributes in code is irrelevant in XML. For example, in the previous code you could put the imageMso attribute above the label attribute and nothing would change. You'll explore various additional attributes later in this chapter.

However, in XML the order of elements does matter. XML is hierarchical (also known as nested or a tree), meaning that you need to put one thing inside another in the proper order, like a Russian doll set. When working with the Microsoft Ribbon, the order of the element tags is Ribbon, tabs, tab, group, button (or other control), as you can see in this code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
     <ribbon>
          <tabs>
               <tab idMso="TabView">
                    <group id="CustomViewsGroup"
                     label="Next Window"
                     insertAfterMso="GroupWindow">
                         <labelControl id="null"/>
                         <button idMso="WindowNext"/>
                    </group>
               </tab>
          </tabs>
     </ribbon>
</customUI>

If you violate this nesting structure—by putting a group tag outside a tab tag, for example—you'll get an error message ("The name in the end tag must match the name in the start tag"):

<button idMso="WindowNext"/>
    </tab>
</group>

Or if you leave out one of the closing tags (signified by the />), you'll get the same error message (here the closing </tab> tag is missing):

</group>
               </tabs>

Using Built-in Icons

The imageMso "gallery" can be downloaded from this web page:

http://www.microsoft.com/downloads/details.aspx?familyid=12B99325-93E8-4ED4-8385-74D0F7661318&displaylang=en#Overview

Load this worksheet into Excel, and then click the Developer tab in Excel to see lists of all the built-in icons (imageMso) you can add to your Ribbon buttons. Hover your mouse over any icon to see its name so you can replace diamond with your chosen icon's name in the imageMso attribute XML code: imageMso="diamond". It's a very large set of icons, many of them easily remembered and thus useful.

Working with Excel and PowerPoint

To modify Excel's or PowerPoint's Ribbons, you use the same techniques demonstrated with Word's Ribbon in the previous section. To illustrate this, the following example hides PowerPoint's Background group in the Ribbon's Design tab.

  1. Start PowerPoint, which automatically creates a new blank presentation.

  2. Click the File tab on the Ribbon; then click Save As, A and save the document as PPMod.pptx to your Desktop (or another location where you can easily locate it).

  3. Click the File tab on the Ribbon and click Close to close this document.

  4. Run the Custom UI Editor for Microsoft Office.

  5. Choose File

    Working with Excel and PowerPoint
  6. Browse to the PPMod.pptx file that you saved in step 2, and open it.

  7. In the Custom UI tab of the Editor, type the following XML code:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
      <ribbon>
         <tabs>
            <tab idMso="TabDesign">
               <group idMso="GroupBackground" visible="false" />
            </tab>
          </tabs>
      </ribbon>
    </customUI>
  8. Click the icon with the red check mark to validate your XML code. If you don't see the message "Custom UI XML is well formed," you've made a typo in the XML code. Retype it (or copy and paste it from this book's web page).

  9. Choose File

    Working with Excel and PowerPoint

At this point, open PPMod.pptx and click the Design tab. The Background group should now be hidden, and as a result of the freed space, the Themes group should display several additional themes.

Undoing Ribbon Modifications

In the previous two sections, you've modified the Ribbon: first by hiding a group in Word, then by hiding a group in PowerPoint. In other examples later in this chapter, you'll add new groups to the Ribbon.

But what if you want to undo the changes you made to the Ribbon or modify those changes? It's quite easy. Just open the document, presentation, workbook, or template in the Custom UI Editor for Microsoft Office. You will see the XML code that represents your customization. Delete it, or modify it, and then simply validate and save it (overwriting it) back to your hard drive. If you explore the Ribbon technology in depth—working directly with .zip and other files that comprise an Office 2010 document—you'll come to appreciate all the tedious and error-prone steps that the Custom UI Editor saves you.

Selecting the Scope of Your Ribbon Customization

Recall that where you put your VBA macros determines their scope: You can embed a macro in a single document, in a template used by multiple documents, or (for Word) in the Normal.dotm file that is used by all Word documents. (For additional details about scope, see the section "Starting the Macro Recorder" in Chapter 1, "Recording and Running Macros in the Microsoft Office Applications.")

When you create XML code to manipulate the Ribbon, where you store your code determines its scope—much like the way scope works with macros. Here are your options, listed in increasing size of scope:

  • To apply your Ribbon customization to only a single document, just embed it in that document, as illustrated in the two previous examples.

  • To use the custom Ribbon for any document that's based on a certain template, put the XML code in a macro-enabled template file type (file types with the .dotm, .xltm, and .potm filename extensions).

  • Add the XML code to Normal.dotm if you want the custom Ribbon automatically available to all Word documents. Just follow the same steps as in the first example in this chapter, but instead of modifying the RibbonMod.docm file in the Custom UI Editor for Microsoft Office, modify Normal.dotm.

(Normal.dotm can be found in a path similar to this: C:UsersRichardAppDataRoamingMicrosoftTemplates. However, replace Richard with your username.)

Recall that you add custom Ribbons to individual PowerPoint or Excel documents much the same way that you add them to Word—by adding XML code and using the Custom UI Editor. However, it's not that simple to add a global custom Ribbon to PowerPoint or Excel documents because they don't have a direct equivalent to Normal.dotm. Instead, with Excel and PowerPoint, you must create an add-in file to globally customize the Ribbon.

Adding a New Group

In this next example, you'll see how to specify where on the Ribbon you want to place a new, custom element and also where to find the correct control identifier (idMso attribute) for built-in dialog boxes, commands, and controls.

Cautions about Customizing

When customizing the Ribbon for coworkers, think it through first. You could, of course, hide the entire group and then replace it by adding a new group of your own—reproducing some or all of Microsoft's original buttons on the group and adding some new ones of your own. But you should think twice before taking this approach because lots of study has gone into these groupings and most people will find them well organized if not totally intuitive.

Of course, if you're just customizing the Ribbon for yourself and your coworkers won't use it, you can more freely rearrange things.

I frequently like to cycle through open Word documents, and that requires the Next Window feature. The Word Ribbon displays a Switch Windows drop-down list, but I don't want to choose from a list; I want to just click a button to open each active document in turn (like the way you can switch to each active Windows application by pressing Alt+Tab).

The Next Window command is listed in the Commands Not In The Ribbon list in the Word Options dialog box. Normally, you want to avoid filling up the Ribbon with new groups because the Ribbon's built-in buttons will not all be visible in order to make room for your custom groups. This can force the user to have to click the Ribbon to reveal what is perhaps a favorite, frequently used button. But in Word's View tab, there's plenty of room to add a new group.

A good place for our new tab, which we will call Shuffle, is just to the right of the built-in Window group. This will leave the Macros group on the far right of the View tab as it is in the default Ribbon arrangement.

To create a new group (called Shuffle) and a new button (called Next Window) in that group, follow the steps in the first example in this chapter. But replace the XML code in step 9 with this XML code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
     <ribbon>
          <tabs>
               <tab idMso="TabView">
                    <group id="CustomViewsGroup" label="Next Window"
insertAfterMso="GroupWindow">
                         <labelControl id="null"/>
                         <button idMso="WindowNext"/>
                    </group>
               </tab>
          </tabs>
     </ribbon>
</customUI>

There are several things to notice in this code. Each element in the Ribbon (the group, label, and button) is given an identifier, an id—a unique string. And there are two types of IDs: the idMso and the plain id. An idMso refers to a built-in Microsoft command, control, or dialog box (such as the Next Window command or a font dialog box). A plain id refers to a customized element you are adding to the Ribbon: a customized group, a new tab, a button that triggers one of your macros, and so on.

Note that I added a button control to the Ribbon (there are other controls you can place on the Ribbon, such as a combo box, as you'll see in another example shortly).

The code in this example illustrates how to use the XML element id:

group id="CustomViewsGroup"

and you can also use it like this:

labelControl id="null"

The id name that you give to these objects (such as CustomViewsGroup) is not displayed; it's for internal code purposes only. So name them as you wish.

Note that I added a Label control but didn't provide any text caption (I included no Label attribute). Used this way, a Label control acts as a spacer, pushing the Next Window button to the middle of the group area. Without it, the button would be displayed at the top of the Ribbon. Add two of these captionless Label spacer controls if you want to push the button to the bottom of the Ribbon.

In this code, the View tab is specified as the tab we're going to use (<tab idMso="TabView">). The caption that will be displayed on the Ribbon to describe our new group is Next Window, thanks to this code: label="Next Window". The position of our new item on the Ribbon is specified as being to the right of the built-in Window group: insertAfterMso="GroupWindow">.

Two Ways to Find the Correct idMso

But, most important, how did I know that the proper Microsoft name for this Next Window command is WindowNext? If you look up the command in the Choose Commands From drop-down list (Commands Not In The Ribbon) in the Word Options dialog box, it is displayed as Next Window. But if you use Next Window in the code for the idMso (Microsoft built-in command ID), nothing will be displayed on the Ribbon because Next Window is not the correct internal ID. This won't work:

<button idMso="Next Window"/>

You must use:

<button idMso="WindowNext"/>

The quickest way to identify the correct internal Microsoft ID (idMso) for any control or command is to pause your mouse pointer on top of the command in the Options dialog box's Choose Commands From drop-down list, as shown in Figure 31.2. The control name appears in parentheses at the far right of the tooltip that is displayed when you hover your mouse pointer over any command in the list.

If you prefer, you can download a more detailed list of the commands and controls to use with the idMso attribute. You can download lists of all the built-in Office 2010 controls—such as the font dialog box or the Clipboard task pane (ShowClipboard)—from this web page:

www.microsoft.com/downloads/details.aspx?familyid=3F2FE784-610E-4BF1-8143-41E481993AC6&displaylang=en

Hover your mouse pointer over any command in this list to see the correct ID to use in your XML code. In this example, you can see that the Next Window command has an ID of WindowNext.

Figure 31.2. Hover your mouse pointer over any command in this list to see the correct ID to use in your XML code. In this example, you can see that the Next Window command has an ID of WindowNext.

Adding Callbacks

Now it's time to employ some VBA code to respond when the user interacts with the Ribbon. Unlike in the previous example—which provided a button that triggered Word's built-in Next Window feature—you'll now add a button that triggers one of your own macros.

To run VBA code, you insert a callback in the XML code to execute whatever VBA macro you specify. When the user clicks a control, such as a button, the XML code that services this control sends a message to the Office application, telling it that a response is needed (this is very similar to the triggering of a Click event in ordinary VBA programming). The Office application then "calls back" to your VBA procedure—whatever procedure is specified by the onAction attribute you write in the XML code.

Put another way: To create a callback, you type in an attribute in the XML code, specifying which macro you want to execute. It looks like this:

onAction="module1.test"

If you add this attribute to a Button control on the Ribbon, it means that when the user clicks that button, the macro named test located in module1 is executed. (The OnAction attribute is similar to the Click event in VBA. In addition to the Button control, the CheckBox, ToggleButton, and Gallery controls have an OnAction attribute.)

To see exactly how this communication between a button and a VBA macro works, follow these steps (refer to the first example in this chapter if you need additional details about how to carry out these steps):

  1. Create a new, empty Word document.

  2. Press F11 to open the VBA Editor.

  3. Double-click ThisDocument in the Project Explorer to open the Code window for your new, empty Word document. (Make sure you're clicking the correct ThisDocument project if there is more than one document currently open in Word.)

  4. Double-click Module1 to display the Code window; then type in this macro that you will execute via a button on the Ribbon:

    Sub test(control As IRibbonControl)
    
    MsgBox "Hi!"
    
    End Sub

    Notice the argument for this procedure: control As IRibbonControl. This argument is necessary when you're interacting with the Ribbon using a button (or any of the large number of other controls that can be put on the Ribbon). If you don't include this argument, you'll get a "Wrong number of arguments, or invalid property assignment" error message.

  5. Click the File tab on the Ribbon and choose Save As to save this document as RibbonTest.docm. Then close the document.

  6. Run the Custom UI Editor for Microsoft Office.

  7. Choose File

    Adding Callbacks
  8. Browse to the RibbonTest.docm file that you saved in step 5 and open it.

  9. In the Custom UI tab of the editor, type the following XML code:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
      <tabs>
        <tab id="t1" label="Execute">
          <group id="g1" label="Run Test">
    
    <button id="b1"
           label="See Message"
    onAction="ThisDocument.test" />
    
          </group>
        </tab>
      </tabs>
    </ribbon>
    </customUI>
  10. Click the icon with the red check mark to validate the XML code. If the code isn't well formed according to the validation test, you've made a typo in the XML code. Retype it (or better, copy and paste it from this book's web page).

  11. Choose File

    Adding Callbacks
  12. Now, to see your new tab, group, and button, open RibbonTest.docm in Word, click the new Execute tab at the far right of the button, and click the See Message button.

You should see a message box appear with the message "Hi!" in it. If you don't, check out the section "What to Look For If Things Go Wrong" later in this chapter.

Try modifying the code in the previous example to see the effect of some other attributes of the Button control. That control has the following attributes: description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showImage, showLabel, size, supertip, tag, and visible.

Adding Attributes

The Ribbon is quite dramatically modifiable; in fact, you can create it from scratch. In this section, you'll see some additional ways to modify the Ribbon. For this, and the remaining examples in this chapter, we'll use Word because it's the most popular Office application and because modifying the Excel or PowerPoint Ribbons works much the same way, as the second example in this chapter illustrated.

Using Built-in Icons and ScreenTips

Try following the steps in the previous section again, but this time modify the XML code for the button element by adding two additional attributes, like this:

<button id="b1"
         label="See Message"
         imageMso= "ShowTimeZones"
         screentip="Say Hi!"
         onAction="module1.test" />

When you load the .docm file and click the Execute tab that you've added to the Ribbon, you'll now see two changes from the previous example, reflecting the two attributes you added to the button element. First, there is a globe icon (one of the built-in imageMso icons).

Also, when you pause your mouse cursor on top of the button, a tooltip (now called a ScreenTip by Microsoft) is displayed. This attribute can be used to remind the user of the purpose of the button. There are actually two elements to a screentip: the screentip attribute displays a heading in boldface and the supertip attribute displays a normal font "body text" message. You can use either or both. The following code produces the result shown in Figure 31.3:

screentip="Run a Macro"
supertip="Click this button to execute the test macro."

Creating Your Own Icons

Although Microsoft provides an extensive collection of built-in icons, you can also create your own. You can use any .bmp, .jpg, or .ico file. The attribute for a custom icon is image rather than imageMso.

The Custom UI Editor for Microsoft Office provides an easy way to find and employ custom icons. To see how this works, open RibbonTest.docm in the UI Editor, and then click the middle icon in the UI Editor (its screentip is Insert Icons). An Insert Custom Icons dialog box opens. Browse your hard drive until you locate the graphics file you want to use. All permitted graphics file types will be displayed in the browser.

The screentip attribute is shown on top in boldface. The supertip attribute displays the body text in a regular font, below.

Figure 31.3. The screentip attribute is shown on top in boldface. The supertip attribute displays the body text in a regular font, below.

It's not necessary to reduce the size of a large graphics file; it will automatically be reduced for you when displayed.

When you find the file you want, double-click it. The dialog box closes and the name of the file is displayed in a right panel in the UI Editor. Notice that the filename extension, such as .jpg, is stripped off in the UI Editor. This reminds you that you don't use the extension when you add this image to the XML code. I've employed a photo of a rose that I took as an icon in the following code.

The file on the hard drive is rose.jpg, but note that in the code it's referred to merely as rose:

<button id="b1"
          label="See Message"
          image="Rose"
          size="large"
     screentip="Say Hi!"
     onAction="module1.test" />

If you want to make your custom icons look great in the Ribbon, you might want to add some transparency to them so the Ribbon shows through around the icon. You can do this using Corel Paint Shop Pro or Adobe Photoshop. Once you learn the technique, it's not difficult to do.

Using Menus and Lists

Although the Button is the most common control employed on the Ribbon (or indeed in any user interface), you can use other controls as well. For example, if you want to offer the user multiple options—such as choosing between executing three different macros—a drop-down list box or a menu might be preferable to using up Ribbon space by adding three buttons on the top level (what's always visible in a tab) of the Ribbon.

Adding Menus

Here's how to add to the Ribbon a menu that, when clicked, displays three buttons—each of which launches a different macro:

  1. Open RibbonTest.docm in the Custom UI Editor.

  2. Type this XML code into the Custom UI Editor:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
      <tabs>
        <tab id="t1" label="Execute">
          <group id="g1"  label="Favorite Macros">
    
    <menu id="m1" label="Choose a Macro">
     <button id="b1"
         imageMso="InkDeleteAllInk"
         label="Convert Case"
         onAction="ThisDocument.ConvertCase" />
     <button id="b2"
         imageMso="DataRefreshAll"
         label="Replace 5pt with 10pt"
         onAction="ThisDocument.UpSize" />
     <button id="b3"
         imageMso="PictureBrightnessGallery"
         label="Memo Format"
         onAction="ThisDocument.Memo" />
    </menu>
    
          </group>
        </tab>
      </tabs>
    </ribbon>
    </customUI>

    Note that I chose imageMso icons that symbolize the various actions taken by these macros (see Figure 31.4).

    Click the Choose A Macro menu, and the menu items are displayed as a set of buttons, ready to launch various macros when clicked.

    Figure 31.4. Click the Choose A Macro menu, and the menu items are displayed as a set of buttons, ready to launch various macros when clicked.

  3. Click the Validate button in the Custom UI Editor, and if the message says your code has no typos (meaning it is well formed), choose File

    Click the Choose A Macro menu, and the menu items are displayed as a set of buttons, ready to launch various macros when clicked.
  4. Open RibbonTest.docm in Word and press Alt+F11 to open the VBA Editor. Ensure that you have three macros in the ThisDocument module named ConvertCase, UpSize, and Memo. They should look like this:

    Sub ConvertCase(control As IRibbonControl)
    
      MsgBox "convert"
    
    End Sub
    
    Sub UpSize(control As IRibbonControl)
    
      MsgBox "upsize"
    
    End Sub
    
    Sub Memo(control As IRibbonControl)
    
      MsgBox "memo"
    
    End Sub

(These message boxes are mere stubs for testing purposes; your actual procedure will, of course, contain real macros that convert case, resize fonts, and display a memo form.)

You can now return to the document RibbonTest.docm and try out the new menu. Click the Execute tab, and notice the Choose A Macro menu in the Favorite Macros group. A menu is indicated in the Ribbon by a subtle down-arrow icon that the user clicks to display the menu items, as shown in Figure 31.4.

If you prefer, you can send all the menu buttons to the same macro and then choose between them in the VBA code by testing their id attribute (which is passed to the VBA code by the ID property of the control object). The VBA code would look like this:

Sub choosemacro(control As IRibbonControl)

    Select Case control.ID
        Case "b1"
            MsgBox ("button1")
        Case "b2"
            MsgBox ("button2")
        Case "b3"
            MsgBox ("button3")
    End Select

End Sub

For this to work, you also need to modify the XML code in this example so that each button executes this same choosemacro procedure:

<menu id="m1" label="Choose a Macro">
    <button id="b1"
imageMso="InkDeleteAllInk"
            label="Convert Case"
            onAction="ThisDocument.choosemacro" />
    <button id="b2"
            imageMso="DataRefreshAll"
            label="Replace 5pt with 10pt"
            onAction="ThisDocument.choosemacro" />
    <button id="b3"
            imageMso="PictureBrightnessGallery"
            label="Memo Format"
            onAction="ThisDocument.choosemacro" />
</menu>

Adding a DropDown List Control

You can employ a DropDown List control much as you would a menu when you want to offer the user a set of choices. Here's an example of XML code that displays a drop-down list on the Ribbon. This example displays the same choices (three macros) to the user as the previous example. But here we use a drop-down list rather than a menu:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
   <tabs>
      <tab id="t1" label="Execute">
         <group id="g1"  label="Favorite Macros">

<dropDown id="ddlist1"


getSelectedItemIndex="setfirst"

           label="Favorite Macros"
           onAction="ThisDocument.test">

     <item id="i1" label="Convert Case"/>
     <item id="i2" label="Replace 5pt with 10pt"/>
     <item id="i3" label="Memo Format"/>

</dropDown>

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

You create a DropDown List control by adding a dropDown element in the XML code and then adding as many item child elements as you wish within the parent dropDown element. (Remember that XML is very particular about capitalization. If you try to use dropdown rather than dropDown, you'll cause an error because your XML code will not be well formed.)

You should include a getSelectedItemIndex attribute, as illustrated earlier, so the DropDown List control's text box will not initially be blank. This attribute executes a macro named setfirst, which you must write. This macro returns the index number of the item you want to initially display in the DropDown List control's text box. In this example, the macro returns a zero (which means to display the first item). So in this example, Convert Case will be displayed. Here's the macro that causes this to happen by setting the return value argument (passed back in the variable named x) to zero:

Sub setfirst(ByVal control As IRibbonControl, ByRef x)
    x = 0
End Sub

Pay particular attention to this GetSelectedItemIndex attribute. Recall that this attribute transfers execution to a VBA procedure when the document is being loaded (before the user sees the Ribbon), so it is a form of initialization. (For other kinds of initialization, you can use other attributes in the set of built-in get attributes, including getEnabled, getImageMso, getLabel, and getVisible.)

One additional macro must be written as well. This second macro (pointed to by the onAction attribute in the XML code) responds appropriately when the user clicks an item in the list:

Sub test(control As IRibbonControl, id As String, index As Integer)

    Select Case index

        Case 0
            MsgBox ("item1")
        Case 1
            MsgBox ("item2")
        Case 2
            MsgBox ("item3")
    End Select

End Sub

When the user clicks an item in the list, the values of both that item's id and index attributes are passed to the macro. So you can use either one to decide what code to execute. In this example, I'm using the index number, but you could just as easily use the id string (such as Case "i1") to detect the id of each item.

Using a DialogBoxLauncher

Some of the Ribbon's built-in groups display a small arrow in the lower-right corner (see the Font group on Word's Home tab). Click that arrow and a dialog box or task pane appears. This is a way to conserve space on the Ribbon if displaying a whole slew of options at all times would be impractical.

If you want to add a dialog box to one of your custom groups, use the DialogBoxLauncher control by writing some XML code like this:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
   <tabs>
<tab id="t1" label="Execute">
           <group id="g1"
               imageMso="StartAfterPrevious"
               label="Insert Date">

<dialogBoxLauncher>

     <button id="b1"
     onAction="test" />

</dialogBoxLauncher>

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

This DialogBoxLauncher merely transfers execution to a macro (named Test in this example). So, when the user clicks the small arrow at the bottom of the custom group named Execute, the Test macro displays whatever built-in dialog box is appropriate. In this case, the Date And Time dialog box is displayed:

Sub test(control As IRibbonControl)

    Dialogs(wdDialogInsertDateTime).Show

End Sub

The VBA Editor will show you a list of the available dialog boxes as soon as you type the left parenthesis in Dialogs(. (This assumes that you've got the handy Auto List Members feature turned on in the VBA Editor. Choose Tools

Using a DialogBoxLauncher

Test this example. It will insert the current date, formatted as you've specified by choosing an option in the Date And Time dialog box.

Toggling with a Toggle Button Control

A toggle button looks like a regular button, but when clicked, it animates itself. In earlier versions of Microsoft's user interface, shadows were displayed behind a clicked toggle button to make the button a pressed button. Now, however, the shadowing has been, for some reason, removed. When toggled, the button turns yellow. I guess that's somebody's idea of a more helpful visual cue.

If you need to add an on-off control to the Ribbon, you can use either a check box control or a toggle button. Both visually cue users about their status: the check box with a check and the toggle button by turning color and adding a frame.

Either of these controls can be used for two-state situations, such as allowing the user to choose between italic text or no italic text. Here's an example showing how to employ the toggle button. When the user clicks it, the button lights up and looks as if it's been pressed into the Ribbon. When clicked a second time, the yellow lighting effect is turned off.

Your VBA procedure can detect the status of the button—pressed or not—by examining a Boolean argument I named down that is passed to your procedure. Here's how to use it in your VBA code:

Sub test(control As IRibbonControl, down As Boolean)

    If down Then
        MsgBox "Button Down"
    Else
        MsgBox "Button Up"
    End If

End Sub

This procedure will respond to the following XML code you can store in your document by using the UI Editor as described in the various step-through examples throughout this chapter:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
   <tabs>
      <tab id="t1" label="Execute">
         <group id="g1" label="Run Test">

<
toggleButton id="tbutton1"
   label="Click to Toggle"
   imageMso="DeclineInvitation"
   onAction="ThisDocument.test" />

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

Modifying the Ribbon in Access

Access often takes its own, sometimes rather indirect path to accomplish a given task. Ribbon customization is no exception. You can still use the Custom UI Editor for Microsoft Office (or any other XML editor) to validate your XML code, but you can't use it to store the XML in an Access database. You must do that by hand, by storing it in a cell in a special table.

In the following example, you'll see how to modify the Access Ribbon by adding a new tab, group, and toggle button—just as we added these elements to Word's Ribbon in the previous section. When the Access Ribbon has been modified, you can click the new toggle button and see the message "Button Down" or "Button Up." The result is the same as in the previous section, "Toggling with a Toggle Button Control," but the steps to achieve that result in Access are quite dissimilar from those you took to add a toggle button to Word's Ribbon.

Follow these steps:

  1. Open a new blank database by running Access, typing in the name R.accdb (in the right pane), and then clicking the Blank Database button.

    (You could also open Northwind or any other database and modify its Ribbon following these steps, but for simplicity, let's stick with a blank database for this example.)

  2. Right-click the Navigation pane's title bar where it says All Access Options. (The Navigation pane is on the left side.)

  3. Choose Navigation Options from the context menu.

  4. Check the Show System Objects check box in the Navigation Options dialog box. Then click OK to close the dialog box. You're going to create a special table named USysRibbons, and it won't be visible in the Navigation pane if this check box isn't checked.

  5. Now you want to make sure you see some error messages if there's a bug in your Ribbon customization programming. To do this, follow these steps:

    1. Click the File tab on the Ribbon.

    2. Click Options.

    3. Click the Client Settings button and scroll down until you locate the General section near the bottom.

    4. Check the Show Add-in User Interface Errors check box. It's likely already checked because this option is enabled by default in Access (but disabled by default in Word and the other Office 2010 applications).

    5. Click OK to close the dialog box.

  6. Next you'll create your special table. Click the Ribbon's Create tab, and then click the Table Design button in the Tables group on the Ribbon.

  7. In the first column of the first row, type ID for the field number, and in the second column, choose AutoNumber as the data type.

  8. In the second row, type RibbonName and choose Text as the data type.

  9. In the third row, type RibbonXml and choose Memo as the data type. You can see the results in Figure 31.5.

  10. Click the cell where you typed in ID to select it, and then click the Primary Key button in the Tools group on the Ribbon. (You may need to click a tab at the very top named Table Tools.)

    A key symbol is displayed in the record selector.

  11. Click the File tab, then click Save to save your new table. Be sure to name the table USysRibbons.

    Here's the special table you can use in Access to store your XML code when modifying the Ribbon.

    Figure 31.5. Here's the special table you can use in Access to store your XML code when modifying the Ribbon.

  12. Now that you've designed the structure of this special table, it's time to add the data that modifies the Ribbon. Right-click the USysRibbons tab and select Datasheet View.

  13. Ignore the ID field; it will automatically generate ID numbers for you.

  14. Click the RibbonName cell and type Toggle—this is the name that will identify your custom Ribbon (you can choose any name that you wish, but for the purpose of this example, use Toggle).

  15. Enlarge the row to make enough room to view the XML code you'll add to the RibbonXml cell. This step isn't absolutely necessary, but it makes it easier for you to test and modify the XML code. In the title bar between the labels RibbonXml and Click To Add, drag to the right to widen the RibbonXml column.

  16. Right-click the record selector (the small yellow square just to the right of the Navigation pane and just above an asterisk symbol). Choose Row Height from the context menu. Type 300 and click OK to close the Row Height dialog box.

  17. Type the following code into the RibbonXml cell:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
     <ribbon
    startFromScratch="false">
      <tabs>
        <tab id="t1" label="Execute">
          <group id="g1" label="Run Test">
    
    <toggleButton id="tbutton1"
    label="Click to Toggle"
    imageMso="DeclineInvitation"
    onAction="togtest"/>
    
          </group>
    </tab>
      </tabs>
    </ribbon>
    </customUI>

    Note that in Access, unlike in other applications, it is necessary to set the startFromScratch attribute to false in the XML code. (This attribute specifies whether you're creating a brand-new Ribbon or modifying the default Ribbon.)

  18. Right-click the table's tab (it reads USysRibbons in boldface) and choose Close. Answer Yes when asked if you want to save changes to the layout.

  19. Click the File tab and choose Close Database.

Testing Your New Ribbon

Now you're ready to choose your new Ribbon as the default Ribbon for this database. You have to specifically select the custom Ribbon you named Toggle. Follow these steps:

  1. Open the database that you closed in step 18 in the previous exercise.

  2. Click the File tab, and then choose Options to open the Access Options dialog box.

  3. Click the Current Database button.

  4. Scroll down until you locate the Ribbon And Toolbar Options section in the dialog box.

  5. Open the Ribbon Name drop-down list box and select Toggle. (If you didn't name your table USysRibbons in step 11 in the previous exercise, the Toggle option will not appear in the drop-down list.)

  6. Click OK to close the dialog box.

  7. A message informs you that you must close, then reopen this database for this new option to take effect. Do so.

  8. Now you should see a new tab named Execute on the Ribbon, as shown in Figure 31.6.

  9. Click the Execute tab. You should then see the Run Test group and your Click To Toggle button. You can look, but don't touch. Don't click the button yet—you've not yet provided a macro named togtest that will act as an event handler (a callback) for this button. Recall that you have this line of code in your XML: onAction="togtest". That means When I'm clicked, execute the VBA procedure named togtest. But you haven't yet created that procedure in a VBA module. You'll do that in the next section.

Now you've modified the Access Ribbon, adding a new tab named Execute.

Figure 31.6. Now you've modified the Access Ribbon, adding a new tab named Execute.

Adding a Callback in Access

In the previous section, you modified the Ribbon by adding three elements to it: a tab, a group, and a control. Now it's time to see how to add a callback, so that when the user clicks the toggle button control you added to the Ribbon, something actually happens—your VBA procedure executes. To create the VBA callback procedure, follow these steps:

  1. Open the database you modified in the previous section.

  2. Press Alt+F11 to open the VBA Editor in Access. Before you can communicate from the Ribbon's controls to a VBA procedure in Access, you must specify a reference to an object library (this step is not required in the other Office 2010 applications).

  3. Choose Tools

    Adding a Callback in Access

    I'm harping on this because failing to add the Office 14.0 Object Library is an easy mistake to make (at least it was for me—it took me hours to figure this one out). I read that a reference to that library was necessary, but when I opened the References dialog box and glanced at it, I saw Microsoft Access 14.0 Object Library and thought that was it. So I just closed the dialog box without adding the essential reference to the Office 14.0 library.

  4. Click OK to close the References dialog box.

  5. Double-click Module1 in the VBA Editor Project Explorer. If there is no Module1, choose Insert

    Adding a Callback in Access
  6. Type this procedure into the module:

    Sub togtest(control As IRibbonControl, down As Boolean)
    
      If down Then
         MsgBox "Button Down"
      Else
         MsgBox "Button Up"
      End If
    
    End Sub

    This procedure looks at the argument named down, and if it returns a value of True, that means the toggle button has been pressed on the Ribbon. If a value of False is returned, that means the toggle button has popped back out. The code If down Then is just a shorter version of If down = True Then.

  7. Now click your Click To Toggle toggle button on the Access Ribbon. You should see a message box saying "Button Down."

What to Look For If Things Go Wrong

Ribbon customization—though useful and important—is new territory for many programmers. You have to deal with XML as a code platform, some unusual programming techniques (such as writing descriptive "markup" code in the XML and traditional programming code in VBA), communication between the XML and VBA via callbacks, new controls, new enumerations (such as the set of icons you can use with ImageMso), and so on.

Bugs happen even when writing code in a technology that you may have been using for decades, such as Visual Basic. So it's no surprise that Ribbon programming—with its several unique features—is pretty much guaranteed to bother you with bugs too. The following sections describe some strategies for dealing with common Ribbon-related bugs.

Employ Error Message Tools

Here are two preliminary steps to take in your effort to avoid, or cure, bugs:

First, turn on an error-reporting feature that's off by default. It allows you to see certain kinds of error messages if something is wrong with your Ribbon customization. Click the File tab on the Ribbon, then choose Options to open the application's Options dialog box. Click the Advanced button (or if you're working in Access, click the Client Settings button). Scroll down to the General section and ensure that the Show Add-in User Interface Errors check box is selected. (This option is enabled by default in Access, but it is disabled by default in Word and the other Office 2010 applications.) XML itself doesn't display error messages or crash—it just does nothing if there's a problem. In other words, whatever effect you're after doesn't happen: A new tab doesn't appear, a built-in tab isn't hidden, and so on. But if you turn on the Show Add-in User Interface Errors feature, you'll see a descriptive explanation of why the Ribbon was not modified. It will look something like the message displayed in Figure 31.7.

Error messages are your first line of defense against bugs.

Figure 31.7. Error messages are your first line of defense against bugs.

This error message tells you that your error in the XML is located in line 12 in the following code (as error messages sometimes are, it's off by 1):

1.  <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
2.  <ribbon>
3.     <tabs>
4.        <tab id="t1" label="Execute">
5.           <group id="g1" label="Run Test">
6.
7.            <toggleButton id="tb1"
8.                   label="Change me."
9.                   supertip="Dynamically changes this button's label"
10.
11.                  onActon="tix"
12.                 />
13.          </group>
14.       </tab>
15.    </tabs>
16. </ribbon>
17. </customUI>

The message also says that the toggleButton element has no attribute named onActon, which indeed it doesn't. You meant to type onAction, so this a typo. However, most often this error message is caused by improper case. For example, you've spelled the attribute or other code element correctly, but you typed OnAction rather than the "proper" onAction. Remember that XML cares about capitalization and insists that it be exactly as expected. This unpleasant case-sensitivity feature is the source of a great many XML bugs. Always check capitalization when you're tracking down an XML problem. A case variation is a subtle distinction, and easily overlooked.

The second preliminary step to take when fixing bugs is to use an XML editor capable of validating XML code. Such an editor is included with versions of Visual Basic (such as VB 2005). You can find free XML editors for download from places like MajorGeeks.com. Or just use the Custom UI Editor for Microsoft Office tool you downloaded earlier in this chapter. XML editors can give you useful error messages if your XML code is faulty—the specific line where a problem is and, sometimes, a good description of the error. There is overlap between XML validation error messages and those displayed by the Show Add-in User Interface Errors option you turned on in the previous step. But when it comes to fixing bugs, the more the merrier.

Cure Common User Interface Programming Problems

Now let's explore some common error messages and what you can do about them.

Callback Problems: Bad Reference, Missing VBA Procedure, or Security Issues

The first error message we'll look at tells you that there's a problem with a callback you're attempting. The XML code can't execute the onAction attribute (see Figure 31.8). This error message can be caused because you haven't set a reference to the Microsoft Access 12.0 Object Library, as described in step 3 in the section "Adding a Callback in Access" earlier in this chapter (a variation on this error message is described in that section).

When you click your new button on the Ribbon to test it, you might see this error message.

Figure 31.8. When you click your new button on the Ribbon to test it, you might see this error message.

This error message can also be caused by the two problems mentioned in the error message. First, you either mistyped or never wrote the procedure that you're referencing. For example, there is no procedure named tix:

onActon="tix"

Or, second, you need to enable macros by clicking the File tab on the Ribbon, clicking Options, then clicking the Trust Center button in the dialog box, clicking the Trust Center Settings button, and clicking one of the check boxes that permit macros.

Other Callback Problems

The next error message (see Figure 31.9) means that you've either neglected to put in the required argument IRibbonControl. You've put in

Sub trigger()

rather than

Sub trigger(IRibbonControl)
This error message results when you don't provide necessary arguments.

Figure 31.9. This error message results when you don't provide necessary arguments.

Or you've neglected to provide additional required arguments. For example, the Toggle Button control passes two arguments to VBA, the IRibbonControl (which all callbacks always pass) and the status of the button, up or down, a Boolean value, like this:

Sub test(control As IRibbonControl, down As Boolean)

XML Mistakes

The next error I'll describe is easy to make. If you violate the XML nesting structure—by putting a group tag outside a tab tag, for example—you'll get the error message shown in Figure 31.10:

<button idMso="WindowNext"/>
                  </tab>
            </group>

The group is supposed to be inside a tab, not vice versa. Or if you leave out one of the closing tags (signified by the />), you'll get the same error message. Here the closing </tab> tag is missing:

</group>
               </tabs>
When XML tags aren't in the proper order, you'll see this error message.

Figure 31.10. When XML tags aren't in the proper order, you'll see this error message.

However, if you validate your XML code in an XML editor like the Custom UI Editor for Microsoft Office, the editor itself will flag a problem such as this incorrect nesting.

Where to Go from Here

As you've seen, the Ribbon can be customized programmatically in a variety of ways. This chapter shows you the essential techniques. However, there's more to explore. You can experiment with the interesting Gallery control or create dynamic Ribbon effects, such as disabling, hiding, or changing the label on a control during runtime, as conditions warrant. In fact, you can create an entirely new Ribbon from scratch using the startFromScratch attribute.

If you're interested in going deeper into Ribbon programming, here are some useful online resources:

Download the RibbonX Visual Designer

This powerful utility can assist you in all types of modifications to the Ribbon. Take a tour of its features at

www.andypope.info/vba/ribboneditor.htm

Try MSDN white papers

Take a look at Microsoft's online help system for developers—MSDN. It includes a lengthy, three-part tutorial on Ribbon programming, which can be found at

http://msdn2.microsoft.com/en-us/library/aa338202.aspx

This helpful tutorial covers advanced topics such as using COM add-ins and dynamic Ribbon updating; a helpful FAQ; and a complete reference to the many attributes, callbacks, and child elements involved in Ribbon programming. Or try searching Bing for "Customizing the 2010 Office Fluent Ribbon for Developers."

Read all about RibbonX

RibbonX for Dummies (Wiley, 2007) is a solid introductory book to all aspects of Ribbon programming, and it covers advanced topics as well.

Windows Ribbon Development Forum

The Windows Ribbon Development Forum is filled with questions and answers on Ribbon programming. If you run into a roadblock, post your question here and experts will respond.

http://social.msdn.microsoft.com/Forums/en-US/windowsribbondevelopment

Explore Microsoft's Access Team's blog

The following web page and its links focus on Access, and it includes information useful to beginners as well as more advanced Access programmers. It's written by Microsoft's own Access Team, and you can also post questions at this blog:

http://blogs.msdn.com/access/archive/tags/Access+2010/default.aspx

The Bottom Line

Hide a tab on the Ribbon

Modifying the Ribbon involves employing XML attributes—similar to methods and properties—of various Ribbon elements such as tabs, groups, and buttons.

Master It

Some Ribbon-related attributes include the suffix Mso. Examples include idMso and imageMso. What does the Mso mean, and what kind of attributes' names are appended with Mso?

Hide a group

You might want to make an entire Ribbon group unavailable to the user. For example, the Editing group on the Home tab includes three options that most people launch via shortcut keys: Find, Replace and Select. So what's the point of having this group take up space?

Master It

What XML attribute of a group do you set to false to remove that group from the Ribbon?

Create callbacks for event handling

To execute VBA code, you insert a callback in the XML code that will run whatever VBA macro you specify. When the user clicks a control, such as a button, the XML code that services this control sends a message to the Office application, telling it that a response is needed.

Master It

What XML attribute do you use to create a callback?

Manipulate the Access Ribbon

Access often does things differently from the majority of Office applications, and Ribbon programming is no different. You can manipulate the Access Ribbon as freely as in the other applications, but several of the programming techniques differ.

Master It

Where can you store the XML code when programming the Access Ribbon?

Debug Ribbon programming

Most Ribbon programming involves writing two types of code: XML and VBA. Strategies for fixing bugs in XML include validation.

Master It

What is XML validation?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.231.15