Customize the Ribbon

The Ribbon — the strip across the top of the program window that contains groups of commands — is a component of the new Microsoft Office Fluent user interface. The Office Fluent user interface provides a single home for commands in Microsoft Office Access 2007 and is the primary replacement for the menus and toolbars in earlier versions of Access.

As you begin to build more advanced applications with Office Access 2007, you might decide that you want to customize the Office Fluent Ribbon in order to make an application easier to use. For example, you can hide some or all of the default tabs so that users cannot use certain commands, and you can create new, custom tabs that contain only the commands that you want to be available.

In all 2007 Microsoft Office system programs that use the Office Fluent user interface, you use Extensible Markup Language (XML) to customize the Ribbon. Therefore, some basic knowledge of XML is helpful. This article does not cover XML concepts, but it does provide you with a basic Ribbon customization procedure and some example XML that you can modify to suit your needs.

What do you want to do?

Understand Ribbon customization techniques

Create and apply a custom Ribbon

Restore the default Ribbon

Understand the XML example

Understand Ribbon customization techniques

In Office Access 2007, you customize the Ribbon by creating customization XML and then adding code or setting database properties that instruct Access to use that XML when it creates the Ribbon. You can use the XML to hide existing tabs and to add new tabs, command groups, and commands. The procedures in this article show you how to add commands that are built-in to Access (such as Find, Sort, and Save) and how to add commands that run Access macros that you wrote yourself.

There are several places that you can store the XML in, but one of the easiest methods is to store it in a system table in the current database. The process involves creating a system table named USysRibbons, adding your Ribbon XML to it, and then specifying whether the custom Ribbon displays for the database as a whole or for a specific form or report. You can define multiple custom Ribbons — one for the entire application, and additional ribbons for individual forms or reports in the database.

Create and apply a custom Ribbon

The following sections provide step-by-step procedures for creating and applying a custom Ribbon.

Before you begin

Display system tables in the Navigation Pane    By default, system tables are not displayed in the Navigation Pane, so you must first change a setting in the Navigation Options dialog box so that you can see the USysRibbons table after you create it. Use the following procedure:

1. With the database open in Access, right-click the Navigation Bar at the top of the Navigation Pane, and then click Navigation Options on the shortcut menu.

2. In the Navigation Options dialog box, under Display Options, select the Show System Objects check box, and then click OK.

   The Access system tables appear in the Navigation Pane.

Enable the display of add-in user interface error messages    Error messages are a valuable source of information when creating and troubleshooting Ribbon customization XML, so it is a good idea to make Access display them. Use the following procedure:

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

2. Click Advanced.

3. Under General, select the Show add-in user interface errors check box, and then click OK.

Create the USysRibbons system table

Use this procedure to create the USysRibbons system table. Later, you will use this table to store your Ribbon customization XML.

1. On the Create tab, in the Tables group, click Table Design.

2. Add the following fields to the table. Be sure to type the field names exactly as shown.

Field Name	Type	Field Size
ID	AutoNumber	Long Integer
RibbonName	Text	255
RibbonXml	Memo

1. If you want, you can add more fields to this table, such as a Comments field to describe the function of the Ribbon XML.

2. Select the ID field. On the Design tab, in the Tools group, click Primary Key.

3. On the Quick Access Toolbar, click Save, or press CTRL+S. Name the new table USysRibbons.

Add Ribbon customization XML to the USysRibbons table

For the purposes of this example, suppose that you want to prevent the users of your database from using any of the tools on the Create tab. Furthermore, you want to create a new tab called A Custom Tab with just the Paste command on it, as shown in the following illustration.

The XML in the following procedure creates this configuration.

1. In the Navigation Pane, right-click the USysRibbons table, and then click Datasheet View on the shortcut menu.

2. Add the following data to the table. You can copy the XML sample from this article and paste it directly into the table.

ID	RibbonName	RibbonXML
(AutoNumber)	My Tab	<customUI xmlns="http://schemas.microsoft.com /office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab idMso="TabCreate" visible="false" /> <tab id="dbCustomTab" label="A Custom Tab" visible="true"> <group id="dbCustomGroup" label="A Custom Group"> <control idMso="Paste" label="Built-in Paste" enabled="true"/> </group> </tab> </tabs> </ribbon> </customUI>

1. This XML first instructs Access not to "start from scratch" — that is, it specifies that Access should display the default Ribbon tabs. Then, it tells Access to hide just one of the default tabs (the Create tab). Finally, it creates a new Ribbon tab named "A Custom Tab," adds a command group named "A Custom Group" to the tab, and adds the Paste command to the group. For more information about this example and how to customize it to suit your needs, see the section Understand the XML example.

2. Close the USysRibbons table, and then close and reopen the database.

Apply the custom Ribbon

Now that the custom Ribbon XML has been stored in a table, do one of the following — depending on whether you want to apply the Ribbon to the entire database or to a specific form or report.

• Apply the custom Ribbon to the entire database    

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

  2. Click Current Database, and then under Ribbon and Toolbar Options, select the Ribbon Name list, and then click the Ribbon that you want — in this case, My Tab.

  3. Click OK.

• Apply the custom Ribbon to a specific form or report    

  1. In the Navigation Pane, right-click the form or report that you want to apply the custom Ribbon to, and then click Design View on the shortcut menu.

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

  3. At the top of the property sheet, under Selection type, ensure that the object type (Form or Report) is selected in the list.

  4. On the Other tab of the property sheet, click the Ribbon Name list, and then click the Ribbon that you want to display when you open the form or report (in this case, My Tab).

  5. On the Quick Access Toolbar, click Save, or press CTRL+S.

  6. Close the form or report, and then double-click it in the Navigation Pane to reopen it.

     The Ribbon that you selected is displayed.

After you verify that your custom Ribbon works correctly, you can hide the system tables again by doing the following:

1. Right-click the Navigation Bar at the top of the Navigation Pane, and then click Navigation Options on the shortcut menu.

2. In the Navigation Options dialog box, under Display Options, clear the Show System Objects check box, and then click OK.

Top of Page

Restore the default Ribbon

To stop using a custom Ribbon and restore the default Ribbon, use one of the following procedures, depending on whether the custom Ribbon is being used by the entire application or by a specific form or report.

Restore the default application-level Ribbon

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

2. Click Current Database, and then under Ribbon and Toolbar Options, delete the contents of the Ribbon Name box.

3. Close and then re-open the database.

Access displays its default Ribbon tabs. The Ribbon XML remains in the USysRibbons table until you delete it, so if you want to reinstate the custom Ribbon, you can do so by setting the Ribbon Name option back to the value it contained previously.

Restore the default Ribbon for a form or report

1. Open the form or report in Design view.

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

3. At the top of the property sheet, under Selection type, ensure that the object type (Form or Report) is selected in the list.

4. On the Other tab of the property sheet, delete the contents of the Ribbon Name property box.

5. Save, close, and then reopen the form or report.

The Ribbon XML remains in the USysRibbons table until you delete it, so if you want to reinstate the custom Ribbon, you can do so by setting the value in the Ribbon Name property back to the value it contained previously.

Top of Page

Understand the XML example

Here is the XML example that was used earlier in this article and an illustration of the custom Ribbon it created.

<customUI xmlns="http://schemas.microsoft.com /office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab idMso="TabCreate" visible="false" /> <tab id="dbCustomTab" label="A Custom Tab" visible="true"> <group id="dbCustomGroup" label="A Custom Group"> <control idMso="Paste" label="Built-in Paste" enabled="true"/> </group> </tab> </tabs> </ribbon> </customUI>

In this example, the second line of XML sets the startFromScratch attribute to False. Setting the value to False ensures that Access leaves all of the existing tabs intact and adds any new tabs to the right of the existing ones. Setting this attribute to True removes all of the existing tabs and displays only the ones that you create in your XML. Even if you set the startFromScratch attribute to False, you can still hide individual tabs. This is demonstrated by the fourth line of XML, which hides the built-in Create tab. The remaining lines create a custom tab and a custom group and then add the built-in Paste command to the group by using the following line of XML.

<control idMso="Paste" label="Built-in Paste" enabled="true"/>

Add additional groups or controls to your custom Ribbon    You can add more groups and controls to your Ribbon by adding similar lines of XML, and substituting different idMso and label values. For example, to create a control that exports the currently selected object to Excel use the following XML.

<control idMso="ExportExcel" label="Export to Excel" enabled="true"/>

To add the control to the same group as the Paste command, insert the new line of XML immediately before or after the line that creates the Paste command. To create a new group, you can copy, paste, and modify the XML that creates the A Custom Group group above. The following example demonstrates the XML that adds the two controls to the custom group.

<customUI xmlns="http://schemas.microsoft.com /office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab idMso="TabCreate" visible="false" /> <tab id="dbCustomTab" label="A Custom Tab" visible="true"> <group id="dbCustomGroup" label="A Custom Group"> <control idMso="Paste" label="Built-in Paste" enabled="true"/> </group> <group id="dbCustomGroup2" label="Another Custom Group"> <control idMso="ImportExcel" label="Import from Excel" enabled="true"/> <control idMso="ExportExcel" label="Export to Excel" enabled="true"/> </group> </tab> </tabs> </ribbon> </customUI>

This XML adds another group to A Custom Tab. As shown in the following illustration, the new group contains two controls — one that initiates an import operation from Excel, and one that initiates an export operation to Excel.

Note: Each group id and tab id value in a custom Ribbon must be unique.

Learn the idMso value of a command    To learn the idMso value of a built-in command, use the following procedure:

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

2. Click Customize.

3. Move the pointer over the item that you want information about. Access displays the control's idMso value in a ScreenTip, in parentheses.

Add a command to run an Access macro    You can add even more flexibility to your custom Ribbon by adding commands that run Access macros. For example, suppose that you created a macro named MyMacro. To add a command to your Ribbon that runs the macro, add the following line to your XML.

<button id="RunMyMacro" label="Run My Macro" onAction="MyMacro"/>

To add the control to the same group as the Paste command in the earlier example, insert the new line of XML immediately before or after the line that creates the Paste command. The following example demonstrates the XML that adds the command.

<customUI xmlns="http://schemas.microsoft.com /office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab idMso="TabCreate" visible="false" /> <tab id="dbCustomTab" label="A Custom Tab" visible="true"> <group id="dbCustomGroup" label="A Custom Group"> <control idMso="Paste" label="Built-in Paste" enabled="true"/> <button id="RunMyMacro" label="Run My Macro" onAction="MyMacro"/> </group> <group id="dbCustomGroup2" label="Another Custom Group"> <control idMso="ImportExcel" label="Import from Excel" enabled="true"/> <control idMso="ExportExcel" label="Export to Excel" enabled="true"/> </group> </tab> </tabs> </ribbon> </customUI>

Top of Page