databasedev.co.uk - database solutions and downloads for microsoft access

Customize the Ribbon in Microsoft Access 2007

The Ribbon is a new element of the Microsoft Office Fluent user interface. It’s new in Microsoft Office 2007 and is a strip across the top of the program containing the commands. It replaced the old menu in the earlier versions of Microsoft Office.

As you work with Access 2007 and begin to build applications that are more advanced, you might want to start customizing the ribbon in order to make your work easier. You can hide some commands, create new tabs containing only the commands you want to use.

In Microsoft Office Access 2007, the ribbon can be customized by creating customization XML and then adding code or setting database properties that instruct Access to use that XML when it creates the Ribbon. XML can be used to hide or create new tabs, commands or command groups.

This tutorial shows the user how to add commands that are built-in to Access (i.e.: Find, Sort, Save) and how to add commands that run Access macros that the users wrote themselves.

Creating and Using a Custom Ribbon

Before you start customizing the Ribbon you need to display the system tables. By default, they are not displayed in the Navigation pane. So you need to change a setting in the Navigation Options dialog box so that you can see the USysRibbons table after you create it.

Right click the Navigation Bar at the top of the Navigation Pane and click on Navigation Options.

Figure 1 Click to open the navigation Options

Under Display options select Show System Objects and then click OK.

Figure 2 Show System Objects

Once this done, now it’s time to enable the display of add-in user interface error messages. These are a good source of information when creating and customizing a Ribbon.

To do that, click the Microsoft Office Button and the click Access Options.

Figure 3 Open the Access Options

Click on Advanced and under General, select to Show add-in user interface errors, then click OK.

Figure 4 Select to show the errors

Create the USysRibbons system table

You need to create the USysRibbons system table in order to store your Ribbon customization XML in it at a later time.

Do as follows:

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

Make sure to create a table exactly as shown in the Figure below.

Figure 5 Create the USysRibbos system table

Now select the ID field and make it a Primary Key.

Figure 6 Select the Primary Key

Save the table and name it USysRibbons.

Figure 7 Save the table

Add Ribbon Customization XML

Note: This example (the XML coding) is taken from Microsoft Access Help.

For the purpose of the tutorial let’s assume you want to prevent the users from using any of the tools on the Create tab. Then we will create a custom tab containing only the Save option.

In the Navigation Pane, right click the table you’ve just created –UsysRibbons-and then click DataSheet view.

Figure 8 Choose the datasheet view

In the RibbonXML field copy the text as it is in the following Figure.

Figure 9 Create the new tab

Close the ribbon table and then close and reopen the database.

Apply the Custom Ribbon

Now let’s apply the Ribbon we have just created. In order to apply it to the entire database, click on the Microsoft Office button, then click Access options. Under Current Database, go to Ribbon and Toolbar Options, select the Ribbon Name list and click the new Ribbon we created: My Tab. Then Click OK

Figure 10 Select the Ribbon

Access will tell you to close and reopen the database for the changed to take effect.

Figure 11 Notice that the Create tab is gone and there is a new tab called "A Custom Tab"

If you want to add the custom tab only to a form or report, in the navigation pane right click the form (report) and select Design View. In the Properties Sheet on the Other tab click the Ribbon Name list and choose My Tab. Save, close and reopen the Form (Report).

Restore to Default

To stop the use of the custom ribbon for the entire database, go to Access Options, under Current Database go to Ribbon and Toolbar Options and delete the contents of the Ribbon Name box. Click OK, close and reopen the database.

Figure 12 Restore the default ribbons

To restore to default after applying the custom ribbon to a form or report, go to the Properties sheet and in Other tab delete the contents of the Ribbon Name Property box. Save, close and reopen the form (report).

For more on the Microsoft Access Ribbon, and Customizing the Ribbon in Microsoft Access 2007 take a look at http://www.accessribbon.com/

If you still prefer the old Microsoft Access Menus, take a look at Displaying the Classic Microsoft Access Menu and the New Ribbon in Microsoft Access 2007