Customizing the New Access UI

I described the new Access UI in this post and this post way back in March.  Now I'll run through how you program the ribbon UI using VBA.  In a future post (we're still working out the content) I'll do another post on how to do the same thing using macros (so the app will run in disabled mode). 

Customized Ribbons

The ribbon UI presents a ton of new flexibility for us creating Access and for you building apps on top of Access that wasn't there before.  As described in the posts on the ribbons, there are a lot of new control types that can be placed in the ribbon, and there's much more flexibility about how they are presented.  The downside to this additional flexibility is that the ribbons are more complex to create than the old command bars.  Building ribbons isn't beyond the reach of any successful Access user today, but it will take some more work.  The upside is that the end product can be significantly better.

Here's an example of an application with a customized ribbon UI:

(Click image to enlarge)

The app has a rich set of controls, including a dropdown to let different users log on:

And of course after each user logs on, you can fire code to both update the contents of the ribbon itself and to take actions inside the application.  Here is an example of the changes to the ribbon itself:

(Click image to enlarge)

The application can have multiple tabs, just as Access does, and the user switches between them just as in the regular Access ribbons.  Here's the Form ribbon:

(Click image to enlarge)

In addition, custom ribbons can use the full range of controls available in Access.  Here's an example of a dropdown button on the Reports tab:

And an alternate split button presentation for the same data:

Controls on the ribbons can how have "Super Tooltips" (not sure if that is the real name?).  These are extra large tooltips containing more, and richer information than the command bar style tooltips, but that launch in just the same way.

Customizing the Ribbon

There are 5 steps to customizing the ribbons:

• Create the ribbon XML - the ribbon definitions are done through an XML file, and the first step is to build that file.  This is pretty easily done with a simple text editor, but can be made even easier with Visual Studio tools.
• Specify callbacks - next, you need to build small VBA routines that handle ribbon actions like clicking a button or selecting something from a dropdown.
• Create table for XML file - next, you create an Access table to hold the XML you created in step 1.
• Add the ribbon to that table - next you create a record in the table for each custom ribbon, containing the ribbon name and the XML definition of the ribbon.
• Set the Custom Ribbon property - finally, you set the Custom Ribbon property for the database as a whole or for specific forms and reports to load the ribbon you defined in the table above. 

Creating the XML Ribbon Definition

The ribbon definition is a simple XML file and can be easily created in any text editor, but if you're using Microsoft Visual Basic.NET 2005 Express Edition or Microsoft Visual Web Developer 2005 Express Edition, you can get IntelliSense for your code.  Visual Web Developer looks something like this:

(Click image to enlarge)

You can point it at the custom ribbon schema at http://schemas.microsoft.com/office/2006/01/customui and VWD should pick up the correct file, which is called customUI.xsd.  Alternately, you can download the file from http://officeblogs.net/UI/customUI.xsd. 

(Click image to enlarge)

This enables VWD to provide type-ahead IntelliSense for the correct ribbon xml.

(Click image to enlarge)

You can see the XML file for the sample application we're building by clicking here.  The contents of the file are:

1. customUI - this is the top level element for a custom ribbon
2. ribbon - defines the ribbon itself.  Set the startFromScratch attribute = "true" to create a new blank ribbon.  If this is omitted or left blank, the custom ribbon will merge with the existing Access ribbons.
3. tab - creates a new tab in the ribbon
4. group - creates a group (or "chunk" in my ribbon description post).  These are used to logically group controls in the ribbon.
5. id - unique name of a control in the ribbon
6. label - static text displayed with a control
7. button - similar to a command button.  Use the onAction attribute to specify the name of a subroutine in the database which will be called when the button is clicked.
8. dropDown - creates a dropdown list that cannot be updated.  To create a drop down that the user can type in, use a comboBox control instead.
9. imageMSO - specifies the name of a built-in control in Office that can be used as in icon in your ribbon.

Creating VBA Call-backs

Next, you need to write the routines that are called by the ribbon when an action is performed.  Here's what to keep in mind when writing these routines:

• You'll need a reference to the Microsoft Office 12.0 Object Library
• Arguments to Sub routines are fixed

• In many cases, you can use a single routine for many events.  For example, you might have a single subroutine that handles opening forms in your database using button controls in the ribbon.  The tag attribute in the button can be used to store the name of the form, and the generic routine simply opens that form name.  For example, such a routine might look like this:

• To refresh the ribbon, call the Invalidate method on the IRibbonUI object.  This object is passed to you in the onLoad callback when the ribbon is loaded.  You can save an extra copy of this object to use as needed:

You can look at the macros in this example database to get a better idea of the code to write.

Creating the Ribbon Table

Simply create a table in your database called USysRibbons.  This table should have 2 columns:

• RibbonName - type: Text(255)
• RibbonXML - type:  Memo

The table should looks something like this:

Adding the Ribbon XML

Now simple create a record for each ribbon you'd like to create in the table above.  Note that this is already done in the image above.

Set Custom Ribbon Property

Next, you go to the Access Options dialog and under Toolbar Options, choose the ribbon for the selected object.  Again, you can see these settings most easily in the attached database. 

Tips, Tricks, and Additional Information

Here are a few general things to remember:

• If you use VBA callbacks, the database must be opened in enabled mode for the code to run.  You may receive an error in Beta 2 when opening a database with custom ribbons in disabled mode.
• Set a reference to the Office 12.0 Object Library to use IRibbonControl.  You'll get a compile error if you don't have the reference included.
• Elements and built-in names of controls and images may change prior to release.
• To edit the ribbon, and XML editor such as Microsoft Visual Basic.NET 2005 is quite handy.  The Express Edition works great.
• Show system objects in the nav pane to modify the USysRibbons table
• You can hold down the SHIFT key when opening the database to open it with the Access ribbon rather than your custom ribbon.  However, this doesn't work in the Beta 2 build (sorry about that).
• You can show errors in the UI XML by turning on an option in Access.  If you open the Office menu (the round button), and select Access Options / Advanced  and then check "show user interface errors", Access will show a dialog describing any problems in the Ribbon XML.  The dialog will look something like this: 

For additional resources take a look at these sites:

• Jensen Harris's blog category on customization at http://blogs.msdn.com/jensenh/archive/category/11280.aspx
• Office Developer Center topic on the Ribbon at http://msdn.microsoft.com/office/tool/ribbon/default.aspx

Next Time

The next post will be the first of several drilling down into using Access in conjunction with Windows SharePoint Services.