Customizing the Office Fluent User Interface in Access 2007

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.

Summary: Learn how you can create a custom Office Fluent Ribbon for an Access 2007 database by using only Office Fluent extensibility markup XML and macros. Discover how to create a command space without writing any code and also learn about more advanced scenarios that require code. (28 printed pages)

Viki Selca, Microsoft Corporation

Clint Covington, Microsoft Corporation

December 2006

Applies to: Microsoft Office Access 2007

Download Marketing Projects.exe.

Contents

  • Introducing the New Office Fluent User Interface in Access 2007

  • Customizing the Office Fluent Ribbon in Access

  • Scenario: Marketing Projects Database

  • Advanced Scenario: Writing Code

  • Conclusion

  • Additional Resources

  • Appendix

Introducing the New Office Fluent User Interface in Access 2007

One of the most exciting new developer features that Microsoft Office Access 2007 provides is the ability to customize the Office Fluent User Interface (UI) in your application. The Office Fluent UI provides a new user model for exposing commands, and application navigation that is more discoverable and easier for users of the application.

You create XML to change the Ribbon, a component of the new Microsoft Office Fluent user interface (UI). You can create customization files in any text editor. All applications that include the Office Fluent Ribbon use the same extensibility model. Therefore, you can reuse the same Office Fluent UI extensibility XML with a minimum of adjustments. For example, you can reuse the custom XML you create for Access 2007 in Microsoft Office Excel 2007, Microsoft Office Word 2007, Microsoft Office PowerPoint 2007, or Microsoft Office Outlook 2007.

For more information about UI extensibility architecture and the specification of the XML markup, see Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).

The finished Marketing Projects database that is discussed later in this article is available in the accompanying download, Marketing Projects.exe.

Tools for the Job

The following tools and references can help you follow the steps in this article:

  • XML Editor

    If you plan to do much UI extensibility development, it helps to have an XML editor that provides Microsoft IntelliSense. Some free options include Microsoft Visual Basic 2005 Express Edition or Microsoft Visual Web Developer 2005 Express Edition. For more information, see Microsoft Express Editions.

  • customUI Schema

    Schema for validating Office Fluent UI customizations. Download the 2007 Office System: XML Schema Reference.

  • 2007 Office System Lists of Control IDs

    Lists the IDs for built-in controls. These IDs enable you to position your tabs and groups among the built-in Office tabs and groups, or show one of the built-in tabs, groups, or controls in your solution. UI extensibility exposes a set of attributes for each XML element that enables you to specify the appearance and behavior of that element. UI extensibility also exposes a special set of attributes—idMso, insertBeforeMso, insertAfterMso, and imageMso—that enable you to work with built-in controls. Refer to Table 13 for a brief description of the attributes that are available for UI extensibility.

    Download the 2007 Office System Lists of Control IDs.

  • 2007 Office System Icons Gallery

    Lists the image names and IDs for built-in controls. These IDs enable you to reuse images that are included in the built-in Office Fluent Ribbons in Office programs. Relevant attribute: imageMso.

    Download the 2007 Office System Add-In: Icons Gallery.

Customizing the Office Fluent Ribbon in Access

Office Fluent Ribbon extensibility customizations in Access 2007 share some options with other Office applications, but with some important differences. Just as with the other applications in the 2007 Microsoft Office system, you customize the Office Fluent Ribbon in Access by using XML markup. Like other applications, you can use external files that contain XML markup or COM add-ins to integrate Office Fluent Ribbon customizations into your application. However, unlike the other Office applications, because Access database files are binary and cannot be opened as the new Office Open XML Formats files, you cannot customize the Office Fluent Ribbon in Access by adding parts to the database file.

Access does provide excellent flexibility in customizing the Office Fluent UI. For example, customization markup can be stored in a table, embedded in a Visual Basic for Applications (VBA) procedure, stored in another Access database, or linked to from an Excel worksheet. You can also specify a custom UI for the application or for specific forms and reports.

There are four steps to customize the Office Fluent UI for your database application:

  1. Create XML markup in the text editor or XML editor of your choice.

  2. Load XML into the database.

  3. Set database, form, and report properties to show customizations.

  4. Use macros or VBA to handle simple actions, such as button clicks.

For more complex applications, you can write code for the following scenarios:

  • Loading XML from a remote location.

  • Changing Office Fluent UI content dynamically.

  • Loading custom images from the file system or from an attachment field in the database.

Loading XML into Your Database

Access provides the freedom to decide where you want to store your Office Fluent UI XML. You have two options:

  • Store XML in a local table. This is the easy, codeless way to go. All your XML is stored in your database and is loaded automatically at startup.

  • Store XML in a separate resource file, database, or add-in. This method requires you to write some code to load your XML. However, it provides you the flexibility of storing your XML wherever you want. For example, you can create an add-in that stores XML customizations that you can share among all applications. It is most important for loading different XML based on the current user. For example, if the current user is part of a database administrator role, you can load an Office Fluent Ribbon that exposes database administrator features.

Access loads UI extensibility markup during application startup. The customizations that you load at startup are available throughout the application during run time. You cannot load new markup after startup. However, you can change which customization to apply.

Loading XML from a Local Table

Access looks for UI extensibility markup in the user system table USysRibbons. You create this table and populate it with as many records as you want. Each record in USysRibbons contains a well-formed and valid chunk of UI extensibility markup that can be applied to the Office Fluent UI. For customizations to be read correctly, you must include the two fields listed in Table 1. Any additional fields are ignored.

Table 1. Required fields for Office Fluent UI customization

Field Name

Data Type

Description

RibbonName

Text

Unique name that identifies the UI extensibility customizations in the RibbonXML field.

RibbonXML

Memo

RibbonX XML that describes a set of customizations to be applied to the Office Fluent UI.

NoteNote

By default, system tables do not show in the Navigation Pane.

To show system objects in the Navigation Pane

  1. Click the Microsoft Office Button, and then click Access Options.

  2. Click Current Database, and then scroll to Navigation.

  3. Click Navigation Options.

  4. Select the Show System Objects option.

    NoteNote

    This option is not available for Access Data Projects (ADPs). For these, you must use the LoadCustomUI method discussed next.

Loading from Remote Sources

You can load UI extensibility markup in the application by calling the only Office Fluent UI-specific Access VBA object member, the Application.LoadCustomUI method. The signature for this method is as follows:

expression.LoadCustomUI(CustomUIName As String, CustomUIXML As String)

Where:

  • expression returns an Application object.

  • CustomUIName represents the name of the Office Fluent Ribbon.

  • CustomUIXML contains the XML customization markup.

Typically, to create and make the Office Fluent Ribbon available to the application, you first add a module to the database that has a procedure that calls the LoadCustomUI method, passing in the name of the Office Fluent Ribbon and the XML customization markup. As stated earlier, the XML markup can come from a Recordset object that was created from a table, from a source external to the database (such as an XML file that you must parse into a String), or from XML markup embedded directly inside the procedure. Different Office Fluent Ribbons can be made available by using multiple calls to the LoadCustomUI method, passing in different XML markup—if the RibbonName of each Office Fluent Ribbon and the id attribute of the tabs that make up the Office Fluent Ribbons are unique.

When the procedure is complete, you then create an AutoExec macro that calls the procedure by using the RunCode action. That way, when the application is started, the LoadCustomUI method is automatically executed, and all the custom Office Fluent Ribbons are made available to the application.

We discuss how to implement this function and load customizations from another location in the Advanced Scenario: Writing Code section.

Bypassing Customizations

Hold down the SHIFT key when you open the database to open it with the default Office Fluent Ribbon in Access instead of with your custom Office Fluent Ribbon.

Errors

You can turn on verbose error reporting for the Office Fluent Ribbon by selecting an option in the Access Options dialog box. This property applies to all 2007 Microsoft Office system programs that have the Office Fluent Ribbon.

To show Office Fluent Ribbon error messages in the user interface

  1. Click the Microsoft Office Button, and then click Access Options.

  2. Click Advanced, and then scroll to General.

  3. Select the Show user interface errors check box, and then click OK.

Setting Database, Form, and Report Properties to Show Customizations

Now that you know how to load your XML into the database and view errors, you can learn to display your customizations in the Office Fluent UI. You can assign a Office Fluent Ribbon for the following contexts:

  • Database   Customizations are controlled by the Ribbon Name property on the database.

  • Form or Report   Customizations are controlled by the Ribbon Name property on the form or report object.

The Ribbon Name property is a drop-down list whose contents correspond to the RibbonName field values that you specified at database startup, either through the USysRibbons table or through the Application.LoadCustomUI method call.

Database-Level Customizations

Database-level customizations are applied at startup, immediately after your XML is loaded and your other startup code is completed. The database customization is displayed until you close the database; that is, you cannot assign a different customization to your database without restarting the application. However, you can change the Office Fluent Ribbon that you load for the database through customizations that you load for forms and reports. You should carefully plan which customizations that you apply in which context. This The section Scenario: Marketing Projects Database discusses this concept in detail.

To apply UI customization when the application is started

  1. Follow one of the previously described methods for loading UI customization markup in the database.

  2. Close and then restart the application.

  3. Click the Microsoft Office Button, and then click Access Options.

  4. In the left pane, click Current Database, and then, in the Ribbon and Toolbar Options section, select one of the Office Fluent Ribbons in the Ribbon Name list.

  5. Now, restart the application. The selected Office Fluent Ribbon is displayed.

Form and Report Customizations

Form and report customizations are displayed when the object is loaded or started, and they are rolled back again when the object is closed or loses the focus. You can apply any customizations that you want for a form or report.

Here are two important things to consider:

  • The customizations that you apply in the form or report are additive to what is already displayed in the Office Fluent Ribbon. That is, unless you specifically hide tabs or set the startFromScratch attribute to True, the tabs displayed by a form or report's Office Fluent Ribbon are in addition to the existing tabs.

  • The customizations that you apply in the database take precedence over form or report customizations because they are applied first. For example, if you set the startFromScratch attribute to True in the database Office Fluent Ribbon, you cannot override this setting in the form or report Office Fluent Ribbon.

To assign an Office Fluent Ribbon customization to a form or report

  1. Open the form or report in Layout view. (Design view also works.)

  2. If the property sheet is not displayed, press F4 to display it.

  3. On the Other tab of the property sheet, select one of the Office Fluent Ribbons in the Ribbon Name list.

  4. Save, close, and then reopen the form or report. The Office Fluent Ribbon you selected is displayed.

Access provides a hidden, empty contextual tabset called AccessFormReportExtensibility, which you can use to display Office Fluent Ribbon content for forms and reports. You should use this tabset if you are adding tabs in your form or report customization. There are some advantages to taking this approach:

  • Adding separate tabs for your form or report enables you to move commands into the Office Fluent Ribbon that traditionally are found in the form. You can use icons and icon size to guide the user toward discovering the most important commands for the form.

  • Regular Office Fluent Ribbon tabs appear passively in the background. However, the contextual tabset takes focus when the form or report is first opened. Therefore, positioning your custom tabs in the contextual tabset has the advantage of drawing additional attention to your customizations.

The Scenario: Marketing Projects Database section discusses this technique in detail.

Creating Simple Macro Actions

Access supports a large array of safe macros that can be run when code is not enabled for the database (safe mode). Using macros also makes for a lightweight application because you do not have to add a VBA project for simple actions, such as button clicks. You can implement many scenarios in a codeless database.

It is ideal to handle the onAction callback with a macro because this callback performs an action and does not require you to return a value to the Office Fluent Ribbon.

XML Example

The XML in the following example shows how to define the onAction attribute for controls. The Office Fluent Ribbon calls the MacroName macro in the MacroObjectName macro object.

onAction="MacroObjectName.MacroName"

You create macros to handle the onAction callback in the Scenario: Marketing Projects Database section.

Creating VBA Callbacks

Ribbon controls contain standard attributes that you can set in the XML markup. Table 13 in the Appendix section contains a list of the attributes that are defined by UI extensibility. Most attributes are set when you write the XML. These attribute values are static and cannot be changed after they are loaded into the Office Fluent Ribbon. The attributes of type "callback" can be set dynamically as soon as the XML is loaded into the Office Fluent Ribbon. These attributes require you to specify the name of a VBA function that the Office Fluent Ribbon should call, instead of specifying a static string. You can handle simple callbacks, such as onAction, with a macro. However, you must write a VBA function to handle any callback that requires you to return a value or object to the Office Fluent Ribbon. In Access, you must also specify a VBA callback to load custom images. This is covered in the Advanced Scenario: Writing Code section.

An advantage of VBA callbacks is that your code can reside in an add-in. One limitation is that code must be enabled in the database for the code to run.

Loading Images

Because Access database files are binary and cannot be opened as the new Office Open XML Formats files, you must follow procedures that differ from other 2007 Office system applications for storing custom images and loading them into the Office Fluent Ribbon. Your options for loading images for your Office Fluent Ribbon in Access are as follows:

  • Reuse an Office icon   Use the imageMso attribute to reuse an icon that is available in Office. This option is available in all Office applications that implement the Office Fluent Ribbon. You can use any icon that appears in any Office application.

  • Use getImage callback   Use the getImage attribute to specify a function that provides the image. You can also use this method in other Office applications. In Access, you have the option of loading images from an attachment field in an Access database.

Reusing an Office Icon

This is the codeless way of supplying images for your Office Fluent Ribbon in Access. Each image that appears on the Office Fluent Ribbon in Office applications has a unique ID that you can use to reference and display it in your custom Office Fluent Ribbon. You can use the imageMso attribute to specify the name of a built-in image to use.

XML Example

The following example shows how to use the imageMso attribute to reference a built-in image.

<button id="cmdCasesByCategory" label="Cases By Category" imageMso="ViewsReportView" onAction="Ribbon.CasesByCategory" />

Loading from the File System

You can use the Office OLE Library to load images from the file system. The Office Fluent Ribbon expects you to return images in the IPictureDisp object. The LoadPicture method can read in BMP or PNG image files and save them to this object.

XML Example

In the following example, a button control is declared with the getImage attribute. The Office Fluent Ribbon calls the function onGetImage to get the image for the button.

<button id="myButton" getImage="onGetImage">

VBA Example

The following example shows the VBA function that is called when the Office Fluent Ribbon evaluates the XML in the previous example.

Function onGetImage(control As IRibbonControl, ByRef image)          
    Select Case control.ID
    Case "myControl":
        Set image = LoadPicture("c:\images\MyImage.bmp")
    End Select
End Function

Loading from an Attachment Field

Access provides ideal storage for images in the new attachment field. If you are using custom images, you should store your images in an attachment field for the following reasons:

  • Images are stored in the database and travel with the application.

  • Attachment control provides an easy, no-fuss method for loading images into the database.

To use this method, you must have the following objects:

  • A table to store images.

  • A form to display load, edit, and display images.

  • A function to return images to the Office Fluent Ribbon.

Creating a Table to Store Images

You can store Office Fluent Ribbon images in any table that you want. You should use the same convention as with XML storage, and name your table "USysRibbonImages".

NoteNote

By default, system tables do not appear in the Navigation Pane. Use the Loading XML from a Local Table to show system objects.

Table 2. Creating a database table for images

Field Name

Data Type

Description

Images

Attachment

Contains the set of BMP, PNG, or JPG images that are used in the application. You retrieve this set of images and search for the appropriate control by name.

In the following example, all images are stored in a single record inside the table. If you use this approach, you should name your images something meaningful so that you can search the field and retrieve the image later. The advantage of storing your images in this manner is that it greatly simplifies your code. The disadvantage is that this method does not scale up for a solution that might require hundreds of custom images.

Table 3. Storing images in a single record

Field Name

Data Type

Description

ControlId

Text

The identifier for the Office Fluent Ribbon control that uses the images in the Images field. You use this field to retrieve the images contained in the Images field.

Images

Attachment

Contains one or more BMP, PNG, or JPG image that is associated with the control in ControlId.

Description

Text

Optional description of this image and the context in which it is used.

Another approach is to expand the schema to include additional fields that track image name, unique ID, and other information that you need to track for your solution. By using this approach, you have one record for every unique image for your application. If you have alternative forms of a single image, such as a small size, large size, transparent background, localized version, and other forms, you can group those together in the attachment field for that record. The advantage to this approach is that it can scale up for solutions that require many images and for solutions where you might swap different images for a control depending on context. The disadvantage is that there is more to track, and your code for retrieving images requires some additional logic.

Creating a Form to Work with Images

You need a basic form that is based on the USysRibbonImages table (or whatever you named your table) to work with the images.

To create a form

  1. In the Navigation Pane, click the USysRibbonImages table.

  2. On the Create tab, in the database window, click Form.

  3. Save the form with the name USysRibbonImages.

Access automatically creates an attachment control and binds the report to the correct record source for you. No additional design steps are necessary. Now you can click in the attachment control to load images into your database. In the next step, you automate this form and use the attachment control to load images.

NoteNote

The number of records that you need to store your images depends on the approach you took with the schema in the previous section.

You write code to load custom images into your database in the Advanced Scenario: Writing Code section.

Scenario: Marketing Projects Database

You have seen how to customize the Office Fluent Ribbon and add images. Now, it is time to put the new knowledge to use, by working with the Marketing Projects database available in Access 2007. As with any software project design, you should understand the customer scenarios and design requirements. The design goal for this project is to provide an intuitive and natural user interface that focuses on tracking a project and its status. The design goals for this application are as follows:

  • Focus on the most important aspects of the application by simplifying navigation between the home screen and other forms.

  • Remove commands from the UI that are not relevant or that can potentially destabilize the database if they are used by a novice user.

  • Reclaim as much vertical space as possible. This includes moving commands that were previously located in the in-form toolbar to the Office Fluent Ribbon.

NoteNote

The finished Marketing Projects database is available in the download that accompanies this article, Marketing Projects.exe.

Step 1: Create the Marketing Projects Database

Follow these steps to create the Marketing projects database:

  1. Start Access 2007.

  2. To select the Marketing Projects database template from the Getting Started with Microsoft Office Access screen, click Featuring in the left pane, click Marketing projects, and then click Download.

    NoteNote

    This step assumes that you are connected to the Internet or have previously downloaded the Marketing Projects template from Microsoft Office Online.

Step 2: Create a Table to Store Office Fluent Ribbon Customizations

Follow these steps to implement custom Office Fluent UI so that it is available when the application starts:

  1. On the Office Fluent Ribbon, click the Create tab.

  2. Click Table.

    A new table appears.

  3. Create the table by using the steps provided in the Loading XML from a Local Table section.

  4. Name your table USysRibbons, and then save it.

Step 3: Paste the UI extensibility Markup

Next, hide the default Office Fluent Ribbon.

  1. Add a record in your USysRibbons table by using the content shown in Table 4.

    Table 4. Using the startFromScratch attribute

    Field

    Content

    RibbonName

    Home

    RibbonXML

  2. Close and then restart the application.

  3. Click the Microsoft Office Button, and then click Access Options.

  4. Click Current Database in the left pane.

  5. In the Ribbon and Toolbar Options section, select the Home Ribbon in the Ribbon Name list.

  6. Close and restart the application.

The startFromScratch attribute tells the Office Fluent Ribbon not to display the default content. Your application should resemble Figure 1.

Figure 1. Start from scratch for the Office Fluent Ribbon

Start from scratch for the Ribbon

Now you are ready to create your own customized Office Fluent Ribbon.

Step 4: Create a New Home Tab

In this step, you create a new tab and a group within that tab where you add controls.

Modify the record in your USysRibbons table so that it contains the content in Table 5.

Table 5. Creating a new Home tab

Field

Content

RibbonName

Home

RibbonXML

Close and then restart the application to see the changes to the Office Fluent Ribbon. Your application should resemble Figure 2.

Figure 2. New Home tab with Projects group

New Home tab

Step 5: Create Controls for the Home Tab

Now you add some controls to the new tab to guide the user through your application.

Creating a Simple Button

Modify the record in your USysRibbons table so that it contains the following content.

Table 6. Creating a Home button

Field

Content

RibbonName

Home

RibbonXML

Close and then restart the application to see the changes to the Office Fluent Ribbon. Your application should resemble Figure 3.

Figure 3. Home tab with new Home button

Home tab with new Home button

Looking at the details of the XML:

  • The imageMso attribute refers to a Office Fluent Ribbon image name that is pulled from the 2007 Office system Icon Gallery.

  • The onAction attribute invokes a macro named Office Fluent Ribbon.MarketingProjectsHome that creates a macro object named Office Fluent Ribbon. This object contains an action called MarketingProjectsHome that calls the OpenForm action to open the Marketing Projects Home form.

Creating a Macro to Handle the onAction event of the Office Fluent Ribbon

  1. In the database window, on the Create tab, click Advanced, and then click Macro.

  2. In the Macro window, add the following action:

    Macro Name: MarketingProjectsHome

    Action: OpenForm

    Arguments: Marketing Projects Home, Form, , , Edit,

  3. Right-click the Ribbon tab, and then click Save.

  4. In the Save As dialog box, type Ribbon.

  5. Now that your macro is available, click the customized Home button to try it out.

  6. Add some simpler buttons to open other forms and reports that relate to your marketing projects. You want to advertise the most important objects by using buttons. In the next section, you create a menu with additional, lesser-used reports.

  7. Modify the record in your USysRibbons table so that it contains the content in Table 7.

    Table 7. Adding buttons to the USysRibbons table record

    Field

    Content

    RibbonName

    Home

    RibbonXML

  8. Close and then restart the application to see the changes to the Office Fluent Ribbon.

The Office Fluent Ribbon for your application should resemble Figure 4.

Figure 4: Additional buttons

Additional buttons

Creating a Menu Control

Now you add a menu control to display the remaining reports that relate to marketing projects.

Modify the record in your USysRibbons table so that it contains the content shown in Table 8.

Table 8. Additional buttons

Field

Content

RibbonName

Home

RibbonXML

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon startFromScratch="true">
  <tabs>
   <tab id="tabMain" label="Home">
    <group id="grpProjects" label="Projects">
     <button id="cmdHome" label="Home" onAction="Ribbon.MarketingProjectsHome" size="large" 
     imageMso="OpenStartPage" supertip="Return to the Marketing Projects home page. 
     Hold down the right shift key to open the database with full menus."/>
     <button id="cmdNewProject" label="New Project" imageMso="GoToNewRecord" size="large" 
     onAction="Ribbon.NewProject"/>
     <button id="cmdProjectList" label="Open Projects List" 
     imageMso="ShowDetailsPage" size="large" onAction="Ribbon.OpenProjectsList"/>
     <button id="cmdProjectsAllOpenProjects" label="All Open Projects" 
     imageMso="ViewsReportView" size="normal" onAction="Ribbon.ProjectsAllOpen"/>
     <button id="cmdProjectDeliverables" label="Project Deliverables" imageMso="ViewsReportView" size="normal" 
     onAction="Ribbon.ProjectsDeliverables"/>
     <menu id="mnuProjectsReports" label="More Reports" 
     imageMso ="ViewsReportView" itemSize="large" >
      <button id="cmdProjectsCompletedAndDeferred" label="Completed and Deferred Projects" 
      imageMso="ViewsReportView" onAction="Ribbon.ProjectsCompletedAndDeferred"/>
      <button id="cmdProjectsBalanceSheet" label="Project Balance Sheet" 
      imageMso="ViewsReportView" onAction="Ribbon.ProjectsBalanceSheet"/>
     </menu>
     <button id="cmdProjectsCommonDeliverables" label="Common Deliverables" 
    imageMso="ReviewShareWorkbook" size="large" onAction="Ribbon.ProjectsCommonDeliverables"/>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Close and then restart the application to see the changes to the Office Fluent Ribbon. The Office Fluent Ribbon for your application should resemble Figure 5 and Figure 6.

Figure 5. Office Fluent Ribbon with More Reports button

Ribbon with More Reports button

Figure 6. More Reports options

More Reports options

Creating a Split Button

Now you create two additional groups for objects related to vendors and employees. Each group contains a split button. A split button is a combination of a button and a menu. The main button has its own definition and action, as do the items in the menu. For example, you can open a form in the main button and list detailed options in the menu portion.

Modify the record in your USysRibbons table so that it contains the content shown in Table 9.

Table 9. Adding a split button

Field

Content

RibbonName

Home

RibbonXML

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon startFromScratch="true">
  <tabs>
   <tab id="tabMain" label="Home">
    <group id="grpProjects" label="Projects">
     <button id="cmdHome" label="Home" onAction="Ribbon.MarketingProjectsHome" size="large" 
     imageMso="OpenStartPage" supertip="Return to the Marketing Projects home page. 
     Hold down the right shift key to open the database with full menus."/>
     <button id="cmdNewProject" label="New Project" 
     imageMso="GoToNewRecord" size="large" onAction="Ribbon.NewProject"/>
     <button id="cmdProjectList" label="Open Projects List" 
     imageMso="ShowDetailsPage" size="large" onAction="Ribbon.OpenProjectsList"/>
     <button id="cmdProjectsAllOpenProjects" label="All Open Projects" 
     imageMso="ViewsReportView" size="normal" onAction="Ribbon.ProjectsAllOpen"/>
     <button id="cmdProjectDeliverables" label="Project Deliverables" 
     imageMso="ViewsReportView" size="normal" 
onAction="Ribbon.ProjectsDeliverables"/>
     <menu id="mnuProjectsReports" label="More Reports" imageMso ="ViewsReportView" itemSize="large" >
      <button id="cmdProjectsCompletedAndDeferred" label="Completed and Deferred Projects" 
      imageMso="ViewsReportView" onAction="Ribbon.ProjectsCompletedAndDeferred"/>
      <button id="cmdProjectsBalanceSheet" label="Project Balance Sheet" 
imageMso="ViewsReportView" onAction="Ribbon.ProjectsBalanceSheet"/>
     </menu>
      <button id="cmdProjectsCommonDeliverables" label="Common Deliverables" 
      imageMso="ReviewShareWorkbook" size="large" onAction="Ribbon.ProjectsCommonDeliverables"/>
    </group>
    <group id="grpVendors" label="Vendors">
     <splitButton id="cmdVendorSplitButton" size="large" >
      <menu id="mnuVendor" imageMso ="RecordsAddFromOutlook" itemSize="large" >
       <button id="cmdVendorList"  label="Vendor List" imageMso="RecordsAddFromOutlook" 
      onAction="Ribbon.VendorList"/>
       <button id="cmdVendorAddressBook" label="Address Book" imageMso="ViewsReportView" 
       onAction="Ribbon.VendorAddressBook"/>
       <button id="cmdVendorPhoneList" label="Phone List" imageMso="ViewsReportView" 
       onAction="Ribbon.VendorPhoneList"/>
      </menu>
     </splitButton>
    </group>
    <group id="grpEmployees" label="Employees">
     <splitButton id="cmdEmployeeSplitButton" size="large" >
      <menu id="mnuEmployee" label="My Menu" 
      imageMso ="ViewsReportView" itemSize="large" >
       <button id="cmdEmployeeList"  label="Employee List" 
       imageMso="RecordsAddFromOutlook" onAction="Ribbon.EmployeeList"/>
       <button id="cmdEmployeeAddressBook" label="Address Book" 
imageMso="ViewsReportView" onAction="Ribbon.EmployeeAddressBook"/>
       <button id="cmdEmployeePhoneList" label="Phone List" 
       imageMso="ViewsReportView" onAction="Ribbon.EmployeePhoneList"/>
      </menu>
     </splitButton>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Close and then restart the application to see the changes to the Office Fluent Ribbon. The Office Fluent Ribbon for your application should resemble Figure 7 and Figure 8.

Figure 7. Office Fluent Ribbon with new split button

Ribbon with new split button

Figure 8. Split button options

Split button options

Step 6: Customize the Microsoft Office Button

To give this application even more a custom appearance, in this section, you customize the Microsoft Office Button commands. You put the Compact and Repair command at the top level and hide the Open, New, and Save As menu commands.

Modify the record in the USysRibbons table so that it contains the content shown in Table 10.

Table 10. Customizing the Microsoft Office Button

Field

Content

RibbonName

Home

RibbonXML

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon startFromScratch="true">
  <officeMenu>
   <button idMso="FileCompactAndRepairDatabase" insertBeforeMso ="FileCloseDatabase" />
   <button idMso="FileOpenDatabase" visible="false"/>
   <button idMso="FileNewDatabase" visible="false"/>
   <splitButton idMso="FileSaveAsMenuAccess" visible="false" />
  </officeMenu>
  <tabs>
   <tab id="tabMain" label="Home">
    <group id="grpProjects" label="Projects">
     <button id="cmdHome" label="Home" onAction="Ribbon.MarketingProjectsHome" size="large" 
      imageMso="OpenStartPage" supertip="Return to the Marketing Projects home page. 
      Hold down the right shift key to open the database with full menus."/>
     <button id="cmdNewProject" label="New Project" imageMso="GoToNewRecord" size="large" 
     onAction="Ribbon.NewProject"/>
     <button id="cmdProjectList" label="Open Projects List" 
     imageMso="ShowDetailsPage" size="large" onAction="Ribbon.OpenProjectsList"/>
     <button id="cmdProjectsAllOpenProjects" label="All Open Projects" 
      imageMso="ViewsReportView" size="normal" onAction="Ribbon.ProjectsAllOpen"/>
     <button id="cmdProjectDeliverables" label="Project Deliverables" 
      imageMso="ViewsReportView" size="normal" onAction="Ribbon.ProjectsDeliverables"/>
     <menu id="mnuProjectsReports" label="More Reports" imageMso ="ViewsReportView" itemSize="large" >
      <button id="cmdProjectsCompletedAndDeferred" label="Completed and Deferred Projects" 
      imageMso="ViewsReportView" onAction="Ribbon.ProjectsCompletedAndDeferred"/>
      <button id="cmdProjectsBalanceSheet" label="Project Balance Sheet" 
      imageMso="ViewsReportView" onAction="Ribbon.ProjectsBalanceSheet"/>
     </menu>
      <button id="cmdProjectsCommonDeliverables" label="Common Deliverables" 
      imageMso="ReviewShareWorkbook" size="large" onAction="Ribbon.ProjectsCommonDeliverables"/>
    </group>
    <group id="grpVendors" label="Vendors">
     <splitButton id="cmdVendorSplitButton" size="large" >
      <menu id="mnuVendor" imageMso ="RecordsAddFromOutlook" itemSize="large" >
       <button id="cmdVendorList"  label="Vendor List" 
       imageMso="RecordsAddFromOutlook" onAction="Ribbon.VendorList"/>
       <button id="cmdVendorAddressBook" label="Address Book" 
       imageMso="ViewsReportView" onAction="Ribbon.VendorAddressBook"/>
       <button id="cmdVendorPhoneList" label="Phone List" imageMso="ViewsReportView" 
       onAction="Ribbon.VendorPhoneList"/>
      </menu>
     </splitButton>
    </group>
    <group id="grpEmployees" label="Employees">
     <splitButton id="cmdEmployeeSplitButton" size="large" >
      <menu id="mnuEmployee" label="My Menu" imageMso ="ViewsReportView" itemSize="large" >
       <button id="cmdEmployeeList"  label="Employee List" imageMso="RecordsAddFromOutlook" 
       onAction="Ribbon.EmployeeList"/>
       <button id="cmdEmployeeAddressBook" label="Address Book" 
       imageMso="ViewsReportView" 
       onAction="Ribbon.EmployeeAddressBook"/>
       <button id="cmdEmployeePhoneList" label="Phone List" 
       imageMso="ViewsReportView" 
       onAction="Ribbon.EmployeePhoneList"/>
      </menu>
     </splitButton>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Close and then restart the application to see the changes to the Office Fluent Ribbon. The Microsoft Office Button for your application should resemble Figure 9.

Figure 9. New Microsoft Office Button options

New Microsoft Office Button options

Remember, you can reuse any internal command by using the idMso attribute and pointing it to the name of the command. The insertBeforeMso attribute and the insertAfterMso attribute enable you to control the position of your command respective to other built-in commands.

Step 7: Create Customizations for the Vendor List Form

Now that you have customized the main Office Fluent Ribbon for your database, now you can customize the application forms. In this section, you create customizations for the Vendor List form. You can apply the same procedure to all the forms in your application with which you want to associate the Office Fluent Ribbon customizations.

Create a new record in your USysRibbons table so that it contains the content shown in Table 11.

Table 11. Creating a new table record

Field

Content

RibbonName

VendorList

RibbonXML

Close and then restart the application to see the changes to the Office Fluent Ribbon. The Office Fluent Ribbon for your application should resemble Figure 10.

Figure 10. Office Fluent Ribbon changes

Ribbon changes

The following is worth noting in the XML:

  • The <tabSet idMso="TabSetFormReportExtensibility"> element tells the Office Fluent Ribbon to insert the content into the correct tabSet. The default name for the tabSet is the Caption property of the current object. By default, if you do not specify a caption, Access uses the object name.

  • The <button idMso="ExportExcel" label="Export to Excel" size="normal"/> element does not have an onAction callback specified. This is because you are reusing a built-in command. When you click the button, the Office Fluent Ribbon executes the default action that is defined for this command. If you want to reuse this command, you can specify your own action in the declaration.

The commands on the Form tab fall into two categories. The List Commands group contains commands that apply only to this form. The remaining tab content consists of built-in groups that contain data entry and analysis tools that are applicable in all the forms in your database.

Step 8: Create Customizations for Address Book Report

You use the technique that you used in the previous section to add customizations for a report.

Create a new record in your USysRibbons table so that it contains the following content.

Table 12. Adding customizations for a report

Field

Content

RibbonName

Reports

RibbonXML

Close and then restart the application to see the changes to the Office Fluent Ribbon. It should resemble Figure 11.

Figure 11. Report tab

Report tab

The procedure for assigning customizations to a report is identical. However, the design of the Office Fluent Ribbon is different. The Report tab contains generic commands that apply to all the reports in your application.

Advanced Scenario: Writing Code

Next, you learn how to implement more advanced UI extensibility scenarios in Access that require you to write VBA code. You must write code for the following scenarios:

  • Loading XML from a remote location

  • Implementing a callback

  • Loading custom images

Loading Code from a Remote Location

To load XML customizations from a location other than the USysRibbons local table, follow these steps:

  1. Create a function that knows how to read your XML data from the desired location.

  2. Create the AutoExec macro to call your loading function at startup.

  3. Create a module and a function to load the XML from your location.

First, you create a location for your XML.

To load code from a remote location

  1. Rename your USysRibbons table.

    As a result, Access will no longer automatically load XML customizations from this table.

    Next, create the VBA module and macro that loads the Office Fluent Ribbons at startup:

  2. Start Access and open a new or existing database.

  3. On the Database Tools tab, click Visual Basic.

  4. On the Create tab, click Advanced, and then click Module.

  5. On the View menu in the Visual Basic Editor, click Properties Window.

  6. In the Properties window, in the Name property text box, change the name of the module to RibbonLoader.

  7. In the code window, insert the following VBA code:

    Option Compare Database
    
    Function LoadRibbons()
    Dim i As Integer
    Dim db As DAO.Database
    Set db = Application.CurrentDb
    
    For i = 0 To (db.TableDefs.Count - 1)
        If (InStr(1, db.TableDefs(i).Name, "Ribbons")) Then
            Dim rs As DAO.Recordset
            Set rs = CurrentDb.OpenRecordset(db.TableDefs(i).Name)
            rs.MoveFirst
    
            While Not rs.EOF
                Application.LoadCustomUI rs("RibbonName").Value, rs("RibbonXml").Value
                rs.MoveNext
            Wend
            rs.Close
            Set rs = Nothing
        End If
    Next i
    
    db.Close
    Set db = Nothing
    End Function
    

    This code creates a Recordset from any table that contains the word "Ribbons" in its name. Then it calls the LoadCustomUI method to load the Office Fluent Ribbons to make them available to the database. And finally, it closes the recordset and the reference to the DAO.Database object.

Implementing a Callback

In the following example, the group element is declared with the label attribute. The label for this group is static. This means that it is set when the group is created:

<group id="Navigation" label="Navigation">

In the following example, the group element is declared with the getLabel attribute. When this group is created, the Office Fluent Ribbon calls the getLabel function to get the value for the label.

<group id="Navigation" getLabel="onGetLabel">

The following example shows the VBA function that is called when the Office Fluent Ribbon evaluates the XML in the previous example.

Function onGetLabel(control As IRibbonControl, ByRef label)          
    Select Case control.ID
    Case "Navigation":
        Label = "dynamic label text!"
    End Select
End Function

The function signatures for a callback might vary depending on the control.

Loading Custom Images

The following VBA examples show how to implement the getImage callback function to load images from an attachment field in the current database. This function uses the form that you created in the previous step to load images.

In this example, all the images for the application are stored in the same record.

Option Compare Database
Option Explicit
Dim formOpened As Boolean
Dim attach As Attachment

Function GetImage(imageId As String, ByRef image)
    If (Not formOpened) Then
        DoCmd.OpenForm "USysRibbonImages", acNormal, , , acFormReadOnly, acHidden
        Set attach = Forms("USysRibbonImages").Controls("Images")
        formOpened = True
    End If
    Set image = attach.PictureDisp(imageId)
End Function

In this function, the USysRibbonImages form is opened as read-only and in hidden mode. This means that you do not see it in the UI. Next, the images are retrieved from the Images control. Then the image whose name corresponds to the imageId that is requested by the Office Fluent Ribbon is returned.

In this example, images for the application are stored in different records. By default, if a record contains more than one image in the Images field, the first image is retrieved.

Function GetImage(imageId As String, ByRef image)
    Static frmRibbonImages As Form_USysRibbonImages
    Static rsForm As DAO.Recordset2
    Dim rsAttachments As DAO.Recordset2
    If frmRibbonImages Is Nothing Then
        DoCmd.OpenForm "USysRibbonImages", WindowMode:=acHidden
        Set frmRibbonImages = Forms("USysRibbonImages")
        Set rsForm = frmRibbonImages.Recordset
    End If
    
    rsForm.FindFirst "ControlID='" & control.ID & "'"
    If rsForm.NoMatch Then
        ' No image found
        Set image = Nothing
    Else
        Set image = frmRibbonImages.RibbonImages.PictureDisp
    End If
End Function

Conclusion

The ability to customize the Office Fluent Ribbon in applications is one of the most exciting new developer features in Access 2007. In this article, you learned how to create a custom Office Fluent Ribbon for an Access 2007 database by using Office Fluent extensibility markup XML and macros. You also learned about more advanced scenarios that require VBA code. The Office Fluent Ribbon provides a new user model for exposing commands and application navigation that is more discoverable and easier for users of the application. Taking advantage of this new UI and its extensibility features can also help make your applications look fresh and new.

Additional Resources

For more information, see the following resources:

Appendix

Table 13 provides a brief overview of the attributes available in UI extensibility.

Table 13. UI extensibility attributes

Attribute

Type or Value

Description

description

String

Description property shown in menus with itemSize="large".

enabled

True, False

Control's enabled state.

getDescription

callback

Gets the description of a control.

getEnabled

callback

Gets the enabled state of a control.

getImage

callback

Gets the image of this control.

getLabel

callback

Gets the label of a control.

getPressed

callback

Gets whether the toggle button or check box is down/selected or up/cleared.

getSupertip

callback

Gets the Enhanced ScreenTip (or "Super tooltip") of a control.

getTooltip

callback

Gets the tooltip of a control.

getVisible

callback

Gets the visibility of a control.

id

String

Unique ID that you specify to identify your controls.

idMso

control id

Built-in control ID.

idQ

qualified id

Qualified name.

image

String

In COM, refers to an image string; in VBA, is a relationship ID.

imageMso

control id

Copies a built-in control's icon to a custom control.

insertAfterMso

control id

Position after a built-in control ID.

insertAfterQ

qualified id

Position after a control with a qualified name.

insertBeforeMso

control id

Position before a built-in control ID.

insertBeforeQ

qualified id

Position before a control with a qualified name.

label

String

Control's label.

onAction

callback

Function called when user clicks this control.

pressed

True, False

Whether this check box or toggle button is selected.

showLabel

True, False

Whether to show the control label.

size

normal, large

Image size.

supertip

String

Control's Enhanced ScreenTip (or "Super tooltip").

tooltip

String

Control's tooltip.

visible

True, False

Control's visibility.