Chapter 11: Creating Dynamic Ribbon Customizations (1 of 2)
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
This article is an excerpt from Expert Access 2007 Programming by Rob Cooper and Michael Tucker from Wrox (ISBN 978-0-470-17402-9, copyright Wrox 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
The Microsoft Office Fluent user interface Ribbon, first introduced in Office 2007, provides many new and interesting opportunities for user-interface development in Access applications. Unlike menus, the Ribbon gives you a chance to expose functionality in an application that might otherwise be overlooked. For new users, the Ribbon is designed to reduce the barrier to entry, making it easier to find the item the user is looking for.
In this chapter, you learn how to:
Create ribbon customizations for use in Access applications
Program the Ribbon to provide dynamic user experiences
Use images effectively in ribbon customizations to provide experiences that are fun and easy-to-use
Disable or repurpose built-in controls to provide your own functionality
The code marked as Visual Basic in this chapter is written in Visual Basic for Applications (VBA).
Ribbon customizations that you write for Access applications are generally designed to replace the menu bars and toolbars of applications created in previous versions. Although this may be how you typically create ribbon customizations, it is not the only way that you can use them.
You can create two types of customizations for the Ribbon. You can use the first type of customization with a particular database, which appears when a given database is open in Access. The second type of ribbon customization you can create is in a COM add-in. Because COM add-ins are available for any database open in Access, it stands to reason that ribbon customizations created as part of a COM add-in are also available for any database. In this chapter, we focus on the first type of customization. Refer to the MSDN Web site for more information about creating ribbon customizations as part of COM add-ins.
Ribbon customizations are written in XML that conforms to a defined XML schema that is available for download. The XML schema for developing ribbon customizations includes controls that are defined as elements in XML and attributes of those controls that define their behaviors and appearance. We take a look at some of the control elements and common attributes used on all types of controls later in this chapter.
Before we get started with developing ribbon customizations, let's go through some tips that will help you during development.
By default, you won't see any errors if there are problems with the XML that you've defined for a ribbon customization. To display errors during development, be sure to set the Show add-in user interface errors option in the Advanced page of the Access Options dialog box. Without this option set, ribbon customizations may not load and it may not necessarily be clear why.
When this error is set, Access displays any errors caused by a ribbon customization, as shown in Figure 11-1.
As VBA developers, we are very glad to have IntelliSense. We see it as a great time saver during development because we can use it to help complete text while writing code. IntelliSense is also available when developing the XML for a ribbon customization using Visual Studio 2005.
In order to use IntelliSense in Visual Studio 2005, you need to download the XML schema for ribbon development. The schema is included as a part of the 2007 Microsoft Office System XML Schema reference, which is available for download from the Microsoft Web site.
Once you have the schema, select the customUI.xsd schema for a given XML document in Visual Studio 2005 as follows:
Launch Visual Studio 2005. You can use any version of Visual Studio, including the Express Editions, which are freely available for download from the Microsoft Web site.
In Visual Studio 2005, click File, select New, and then click File.
Select XML File and click Open.
Click the builder button in the Schemas property for the file. This property is available in the Properties window for the file.
Click the Add button and browse to the customUI.xsd file that was installed as a part of the XML Schema reference.
Make sure the customUI.xsd schema is checked and click OK in the XSD Schemas dialog box in Visual Studio.
When the schema has been added to the document, you should receive IntelliSense for the customUI node, as shown in Figure 11-2.
To make this schema easily available in Visual Studio, copy the customUI.xsd file to: C:\Program Files\Microsoft Visual Studio 8\xml\Schemas. The customUI.xsd file will appear in the list of schemas for XML documents in Visual Studio after copying to this location. Replace the path to Visual Studio 2005 as needed.
Prevent Loading at Startup
If you are developing a ribbon customization to replace the built-in Access Ribbon, you may want to prevent your customization from loading in order to use the development tools and ribbons included with Access. To prevent your ribbon customization from loading during development, hold down the Shift key.
Finding Controls and Images
Office 2007 includes a great number of built-in controls and images that you can use in your applications. As you will see later in this chapter, you are not limited to using images from Access. You can use images from other Office applications such as Microsoft Word, Microsoft Excel, and Microsoft Outlook as well! With all of these options available, finding controls and images can be a bit daunting. Luckily, Microsoft has provided some resources to help you find controls and images for use in your applications.
Each application that supports the Ribbon in Office 2007 includes a built-in mechanism for finding controls in the Customize page of the Options dialog box for the application. For example, let's say that you wanted to find the built-in control ID for the Access Close Database button in the Office menu. Here's an easy way to find the control.
Open the Access Options dialog box and select the Customize page. The Access Options dialog box is available under the Office menu in Office 2007.
Select Office Menu from the Choose commands from drop-down.
Hover the mouse over Close Database. You should see a tooltip that provides the name of the button, FileCloseDatabase, as shown in Figure 11-3.
This is most helpful when you know exactly what you are looking for. To prevent wading through dialog boxes looking for a given control, Microsoft provides a download called "List of Control IDs" that contains lists of all control ID values in Office 2007. This is also available for download on the Microsoft Web site.
Microsoft began to release documentation about the Ribbon for developers prior to the release of Office 2007. With the vast amount of changes made to the user interface, this was intended to get the word out early so that developers were prepared. One resource that has been very useful for us is the Office Fluent User Interface Developer Center on MSDN.
How to Write Ribbon Customizations
As mentioned earlier, using IntelliSense to write the XML for a ribbon customization makes authoring the Ribbon much easier. This gives you more time to focus on how you'd like the Ribbon to look and function. With this in mind, we'll use Visual Studio to write ribbon customizations. Later in this chapter, we show you where to save the customization for use in your application.
The root node of a ribbon customization is the customUI node, which defines the XML namespace that is tied to the schema for ribbon customizations in Office 2007. Let's use this node to start writing our first ribbon customization. This first customization is pretty straightforward, but it will introduce you to the process.
Start by creating a new XML file in Visual Studio and add the customUI.xsd schema to the document, as described in the section, "Using IntelliSense."
Next, add the XML for the customUInode, as shown in the code that follows. IntelliSense makes adding this node easier, complete with the namespace.
The first thing you do is tell the Ribbon that we are adding to the built-in Access Ribbon. To do this, use the Ribbon node as shown in the following XML.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> </ribbon> </customUI>
Next, add a tab with one group to the built-in Access ribbon. To do this, add the XML as shown in the following. We discuss tabs and groups in more detail in the section "Organizing Ribbon Items."
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab id="tab1" label="My Tab"> <group id="grp1" label="My Group"> </group> </tab> </tabs> </ribbon> </customUI>
Now that you have a group, you can start to add controls. Many people start programming in Access and VBA with command buttons, so you'll do the same here. Add a button to the group using the button node as shown in the following.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab id="tab1" label="My Tab"> <group id="grp1" label="My Group"> <button id="btn1" label="Hello World" onAction="=MsgBox('Hello World')"/> </group> </tab> </tabs> </ribbon> </customUI>
Note the use of the onAction attribute. This attribute is defined for several nodes in the Ribbon schema and is called when the user invokes some functionality on a control. Attributes on controls that call into custom code are called callbacks. In this case, we're using the MsgBox function in an expression to display the obligatory Hello World message. Using expressions for ribbon customizations in Access is convenient, but this is a programming book. We're here to write code. The remaining callbacks in this chapter use VBA in a database to provide more functionality.
Congratulations! You've written a ribbon customization! You'll see how to add this to a database in a few moments.
The onAction attribute is one you are likely to see frequently. Other attributes that you'll commonly use are listed in the following table.
Specifies whether a control is enabled.
Defines a control ID. Must be unique in the Ribbon customization.
Specifies the custom image for a control. Typically used with the getImage or loadImage callbacks.
Specifies an image for a control that is defined by Office 2007.
Specifies the name of a built-in control that you want your control to appear after.
Specifies the name of a built-in control that you want your control to appear before.
Defines the text displayed for a control. This is similar to the Caption property for a label in Access.
button, gallery, menu, splitButton, toggleButton
Sets the size of a control. Possible values are normal or large.
Specifies whether a control is visible.
Once you've written a ribbon customization, you need a way to load it into Access. There are two ways to do this. The easiest way is to use a user-defined system table called USysRibbons.
Using the USysRibbons Table
The easiest way to load a custom ribbon is to use a special table called USysRibbons. This table has two fields, as shown in the table that follows.
Data Type (Size)
Create this table now to add the Ribbon customization that you created earlier. Add a record to the table and set the RibbonName field to HelloWorld, and copy and paste the XML created earlier in the RibbonXml field.
When Access opens a database, it looks for a USysRibbon stable and loads any ribbon customizations that are defined by the records in the table. Ribbon customizations must be named uniquely throughout the application so you cannot duplicate a RibbonName. Make this field a primary key if you define a lot of ribbons in an application to prevent yourself from creating duplicate names.
Once you've added the HelloWorld ribbon in the table, let's tell Access to load this ribbon when the database is opened. To do this, Access includes a new property in the Current Database page of the Access Options dialog box called Ribbon Name. Set this property to the name of a ribbon customization to load, in our case, HelloWorld.
The drop-down list for this control is populated after closing and re-opening a database.
After setting the property, Access tells you that you need to close and re-open the database for the property to take effect. When you re-open the database, you should have a new tab on the Access Ribbon called My Tab, as shown in Figure 11-4.
The Ribbon Name property is stored in DAO as a database property on the DAO.Database object called CustomRibbonId. Use the following code to retrieve the property.
(Visual Basic for Applications)
Using the LoadCustomUI Method
Obviously any data you store in the database adds to its size. If you are using several ribbons in an application, you might want to store ribbons external to your database. Access 2007 includes a new method on the Application object called LoadCustomUI that allows you to do this.
This method only loads ribbon customizations into Access. It does not update the current ribbon. As a result, you need to tell Access to display a ribbon either by:
Setting the Ribbon Name property for the database, or
Setting the Ribbon Name property for a form or report
We also tend to call this method in a conditional statement as shown in the following example.
(Visual Basic for Applications)
Function LoadRibbonFromCommandLine() As Long Dim stXmlPath As String Dim stXmlData As String ' get the path to the ribbon and make sure it exists stXmlPath = CurrentProject.Path & "\LoadCustomUITest.xml" Debug.Assert (Len(Dir(stXmlPath)) > 0) ' load the ribbon from disk Open stXmlPath For Input Access Read As #1 stXmlData = Input(LOF(1), 1) ' remove the byte order mark stXmlData = Mid(stXmlData, InStr(stXmlData, "<customUI")) Close #1 ' Check the command line. If you pass "DEBUG" to the /cmd switch, ' load the ribbon with startFromScratch="false" If (Command$() = "DEBUG") Then stXmlData = Replace(stXmlData, _ "startFromScratch=""true""", _ "startFromScratch=""false""") End If ' load the ribbon LoadCustomUI "rbnMain", stXmlData End Function
The Ribbon customization being loaded from disk defines the startFromScratch attribute as true. In this function, we are checking the command line using the VBA Command function. If we pass in the string DEBUG, then we change the XML after it has been loaded to set it to false. This is a simple indicator that we are working in a debug version of the database and we want to see the Access Ribbon to do development work. You could use a similar technique to load a different ribbon customization altogether either for yourself or for users in a particular security group.
To get the ball rolling for the application in this example, we have preset the Ribbon Name property to rbnMain and call the LoadRibbonFromCommandLine function from an autoexec macro using the RunCode macro action.
To write ribbon customizations that are flexible and dynamic, you need to write some code. If you were familiar with programming CommandBar objects in previous Office versions, you'll find that things have changed quite a bit.
The biggest difference is that there isn't a direct mechanism for setting properties on the Ribbon. In other words, with a CommandBarControl object, you could set its caption by using something like the following:
(Visual Basic for Applications)
Dim objControl As Office.CommandBarControl Set objControl = CommandBars("Menu Bar").Controls(0) objControl.Caption = "Test Caption"
You cannot change properties of controls in the Ribbon in this manner. Instead, you must use callback routines that are named in attributes in the Ribbon-customization XML. A callback is a procedure that the Ribbon calls when it needs to check the state of an object, such as whether a control is enabled or visible; or when it needs data, such as in a combo box control; or when the user has taken some action, such as clicking a button.
Without an object model that includes properties, such as the CommandBarControl object, most values for controls in a customization are set using the arguments that are defined in the callback code. We take a closer look at this model throughout this chapter.
The callback routines defined by the Ribbon typically include an instance of an IRibbonControl object that defines the control whose callback is being fired. To use this object, set a reference to the Office 12.0 Object Library. Once you've set this reference, you can write callback routines. For example, the signature for the onAction callback for a button is defined as follows.
(Visual Basic for Applications)
The routine can be named any valid procedure name in VBA. In the XML that defines the callback, use the name of the routine as shown.
Using Callback Routines
The Ribbon defines several callback attributes that are used for a number of scenarios as mentioned earlier. Callback routines are similar to events. When the Ribbon determines that it needs some information or when something occurs, it notifies you using callback routines. The good news is that you are given the control for each callback, so you can reuse the code for a callback routine for multiple controls.
For example, if you use buttons in your customizations, you're likely to use the onAction attribute. Let's say that you use several buttons to open forms in your application. Rather than writing a callback for each form, you might store the name of the form in the tag attribute of a control. As with the Tag property of Access controls, the tag attribute of Ribbon controls lets you store extra data with a control. This means you can write one callback routine to handle opening forms such as the following:
<button id="btnMoreOptions" onAction="OnOpenForm" tag="frmOptions" label="More Options"/> <button id="btnHelp" onAction="OnOpenForm" tag="frmHelp" label="Help"/> <button id="btnHome" onAction="OnOpenForm" tag="frmHome" label="Home"/>
Then the code for the callback is straightforward, as follows.
(Visual Basic for Applications)
Common Callback Routines
Several controls define callbacks that are common to many controls. The following table lists callbacks that are common across controls that you may use frequently. We use some of these callbacks later in this chapter in specific scenarios.
Callback attribute that is called when the Ribbon is about to determine whether to enable a control. Enables you to dynamically set the enabled attribute for a control.
Callback attribute that is called when the Ribbon is about to display the text for a control. Enables you to dynamically set the label attribute for a control.
Callback attribute that is called when the Ribbon is about to determine whether to display a control. Enables you to dynamically set the visible attribute for a control.
button, checkBox, dropDown, gallery, toggleButton
Callback attribute that is called when the user takes action on a control.
Similar Properties, Methods, or Events in Access
The following table lists several of the callbacks for specific controls along with the equivalent event for the corresponding control in Access.
Similar Access Event,
Method, or Property
Fires when a button is clicked
Sets the pressed state for a check box
Fires when the checkbox is clicked
Click event or AfterUpdate event
Fires when an item is selected in a combo box
Determines how many items will appear in the combo box
Sets the label for items in a combo box
Sets the initial text in a combo box
Fires when an item is selected in a drop-down
Determines how many items will appear in a drop-down
Sets the label for items in a drop-down
Returns the index of the selected item
Sets the initial text of an edit box
Fires when the text has changed in an edit box
Sets the pressed state of a toggle button
Fires when a toggle button is clicked
Click event or
For a complete list of callbacks with the expected signatures for the routines refer to MSDN.
You may want to refresh controls in your customizations from time to time. For instance, let's say that you were writing an application for a doctor's office and wanted to use a drop-down control to allow users to set their status to either available or away. In the away state, you might want to lock controls in the Ribbon to prevent users from accessing items while the person was away. In order to do this, you need to handle the onLoad callback in the Ribbon.
The onLoad callback is defined on the customUI node as follows.
<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui onLoad="OnRibbonLoad">
The signature for this callback in code receives an IRibbonUI object, which defines two methods: Invalidate and InvalidateControl. Cache a copy of this object in a global variable to call these methods. The callback is called when the customization is loaded and only then. You cannot call it again. Because globals are reset when an unhandled error occurs, test the object for Nothing before calling the method. The following code shows you how to cache the object.
(Visual Basic for Applications)
Public gobjRibbon As IRibbonUI Public Sub OnRibbonLoad(objRibbon As IRibbonUI) Set gobjRibbon = objRibbon End Sub
Then, to refresh, or invalidate all controls in the Ribbon, call:
To refresh an individual control, call:
The top level of the command bar structure in previous versions of Office and Access was the menu. Menus contained controls or other menus that sometimes resulted in deeply nested hierarchies that made items difficult to find. One goal of the Ribbon is to expose commonly used commands so that they are visible to users. Let's look at how you can organize controls in a ribbon customization.
The top-level means of organization for items in a ribbon customization is a tab. Tabs can contain group nodes. To define a custom tab, use the tab node in the customization, such as the following:
The tab node is contained within the tabs node unless you are using contextual tabs as described in the next section.
Tabs that appear for a particular view of an object are called contextual tabs. Access provides several built-in contextual tabs. For example, there are contextual tabs in design view of Forms, Reports, Macros, and Queries, and when a table is open in datasheet view. You'll also find contextual tabs in other applications in Office. For example, Word provides contextual tabs when designing tables.
To create your own contextual tabs in a customization, use the contextualTabs element in XML. This node must contain another element named tabSet. In the tabSet element, specify an idMso attribute of TabSetFormReportExtensibility. This tells Access to provide contextual tabs for a form or report. Follow these steps to create contextual tabs.
Create a new XML file and add the root node and the Ribbon node, as follows.
Add the contextualTabsandtabSetnodes, as follows.
Add the Ribbon customization as normal. In this example, we create contextual tabs that contain two tabs and two groups.
Close the nodes that were created above, as follows.
Add this customization to a USysRibbons table and set the RibbonName field to ctabReport1.
Create a new report named Report1.
Open the report in design view and set the Ribbon Name property of the report to ctabReport1. This list is refreshed when you close and re-open the database. Set the Caption property of the report to My Report: Caption.
Close and re-open the database and open Report1 in Report View. You should see the two tabs that you defined in the customization, as shown in Figure 11-5.
Notice that the caption of the report was used in the title bar over the two tabs that were defined. If the Caption property is empty, the name of the object is used in this title.
Contextual tabs do not appear in design view or layout view of an object.
We have already worked with groups in our customizations. The group node can contain other control types.
The Ribbon provides many different types of controls that you can add to a customization. We've already looked at some of the different types of controls, but let's take a closer look.
The code for the samples that follow is contained in the sample file TestControls.accdb, which is available for download on the Web site for this book.
Buttons are created using the button node and are likely to be the control you use the most when designing a ribbon customization. Buttons can be created in two different sizes — large or normal — as indicated by the size attribute, as shown in the following.
Figure 11-6 shows five buttons, two of which are large and three that are normal size.
As with toggle button controls in Access forms, toggle buttons in a ribbon customization enable you to reflect a true/false state. For example, imagine that you are creating a context-sensitive help system in your application that displays help information in a form. Using a toggle button, you can show or hide the help form.
Toggle buttons are created using the toggleButton node, as shown in the following, and can also appear in normal or large size:
<toggleButton id="tglHelpForm" size="large" imageMso="Help" label="Help Form" onAction="OnPressedAction"/>
The onAction callback for the toggle button is used to hide or show a form called frmHelp, as follows:
(Visual Basic for Applications)
Public Sub OnPressedAction(ctl As IRibbonControl, Pressed) If (ctl.ID = "tglHelpForm") Then If (CurrentProject.AllForms("frmHelp").IsLoaded) Then ' close the form DoCmd.Close acForm, "frmHelp" Else ' show the form DoCmd.OpenForm "frmHelp" End If ' refresh the control If (Not gobjRibbon Is Nothing) Then gobjRibbon.InvalidateControl "tglHelpForm" End If End If End Sub
This customization creates a toggle button, as shown in Figure 11-7.
When you click the toggle button, the form is shown as depicted in Figure 11-7. When you click the toggle button again, the form should be closed.
Check boxes have similar behavior to toggle buttons in that they can show a true/false state. They are created using the checkbox node, as follows.
Figure 11-8 shows three check boxes, one of which is disabled using the enabled attribute with a value of false.
Check boxes can also appear in menus but they appear differently as shown in the previous example. The following XML creates two menus that contain check boxes.
<menu id="mnuCheck1" label="Normal checkboxes"> <checkBox id="mnuchk1" label="Menu checkbox 1"/> <checkBox id="mnuchk2" label="Menu checkbox 2"/> </menu> <menu id="mnuCheck2" label="Large checkboxes" itemSize="large"> <checkBox id="mnuchk3" label="Menu checkbox 1"/> <checkBox id="mnuchk4" label="Menu checkbox 2"/> </menu>
When you check a normal check box in a menu, you should see something that resembles the check box in Figure 11-9.
We'll talk about menus a little later, but you'll also notice that the menu node contains an attribute called itemSize. When you set this attribute to large, the items inside of a menu appear larger. When you select a large check box in a menu you should see something that resembles Figure 11-10.
Combo Boxes and Drop-Downs
As with combo boxes in Access, combo boxes can be used to provide the user with a list of options to choose from. Combo boxes are created using the comboBox node and contain item nodes. Using item nodes in a combo box creates a static list of items as shown in the following XML and in Figure 11-11:
<comboBox id="cboStatic" label="Static combo box"> <item id="cboItem1" label="Item 1"/> <item id="cboItem2" label="Item 2"/> <item id="cboItem3" label="Item 3"/> </comboBox>
To create a combo box with a dynamic list of items, you need to use callbacks. Creating a dynamic combo box is described in the section "The NotInList Event — Ribbon Style."
A drop-down control is very similar to a combo box, but the user cannot type text in it. Drop-down controls are created using the dropdown node. These controls are very useful for giving the user a list of items that cannot change, such as a status. The following XML defines a drop-down control with two items. (When you click the drop-down, you should see the items listed, as in Figure 11-12.)
<dropDown id="ddStatic" label="Static dropdown"> <item id="ddItem1" label="Item 1" imageMso="HappyFace"/> <item id="ddItem2" label="Item 2" imageMso="Info"/> </dropDown>
You'll notice that these controls can also contain images. We'll go into more detail about images later in this chapter.
Labels and Edit Boxes
The Ribbon also defines labels and edit boxes that are similar to labels and text boxes in Access. Label controls can also be disabled, but we tend to leave them enabled to show information. Label controls are useful for displaying status information about data in the application. You might also use them to show the current date and time.
Edit boxes are useful for letting the user enter any information. Later on we'll take a look at using a label control and edit box for form navigation.
Menus are still available in the Ribbon. Their use, however, is limited to cases when there are multiple command choices that are grouped together, crowding the Ribbon. Menus take on the new appearance of the Ribbon, but still let you construct hierarchies as with previous versions of Office. Let's take a look at the different features of menus. In the following sections, we look at two buttons as they appear in menus that have been decorated with the specified features.
As we mentioned earlier, menus contain an attribute called itemSize that lets you control the size of items under the menu. This attribute can be set to either normal or large. Figure 11-13 shows the two buttons when the itemSize is set to normal.
When the itemSize attribute is set to large, you should see something similar to Figure 11-14. We've also set the size attribute of the menu to large so that it fills all three rows of the Ribbon.
The description attribute is available for controls when you set the itemSize attribute of the menu to large. This attribute is used to provide more text inside the menu for a given control. The XML that defines this attribute looks like this.
<menu id="mnuDescriptions" label="Menu: Descriptions" itemSize="large" size="large"> <button id="mnuBtn1D" label="Button1" description="Click here to run something cool"/> <button id="mnuBtn2D" label="Button2" description="Click here to run something even cooler"/> </menu>
The two buttons that show descriptions are shown in Figure 11-15.
Menu separators provide a nice separation of controls in a menu and are created using the menuSeparator node. This control defines an attribute called title that is used to include text in the separator, as shown in the following XML:
This creates a menu separator that contains text, as shown in Figure 11-16.
The title attribute is optional, however, so if you don't define it you get menu separators that look like the flat menu separators from previous versions of Office. These separators appear in Figure 11-17.