How to Hack the Access 2010 & Access 2013 Customized Application Ribbon to Build Custom Ribbons Using Ezy Access Ribbon Builder

Click on the image to zoom

Starting with Access 2010, you can customize the Application Ribbon by right-clicking in the Ribbon-bar and selecting  Customize the Ribbon from the popup menu. You can use macro actions to fire custom commands on the Ribbon, using custom tabs, groups, and buttons. You can assign some very basic icons to custom commands and edit the labels, and you can hide Access development environment tabs, groups, and commands

The limitation is that the customization applies to the Access application and is not stored in the database. You can export the customization to an XML file with the extension .exportedUI. You can then import the file to use that customization in any instance of the Access application.

Custom database specific Ribbons must be built outside Access and loaded at run-time using VBA or the USysRibbon paradigm. For more information on how these custom ribbons are built look at this Access Extra post How to Build an Access 2007 Ribbon.

I have a ribbon builder tool for Access 2007/2010/2103 which is currently in BETA - Ezy Access RibbonX Builder. Details and the free download are here. With this tool you can build custom ribbons from scratch.

I have come up with a way to make the task of building custom Access ribbon easier, and here I will explain how to build a ribbon like the one in the screenshot at the top of this post.

1. I have a database where all the actions I want to include in my custom ribbon are saved as macros. The ribbon I want to build will also comprise some standard Access menu commands.

2. In Access with my database open, I invoke the Access Options dialog and click on Customize Ribbon in the sidebar, and customise the application ribbon as shown below:

Click on the image to zoom

This customization results in this application ribbon:

Click on the image to zoom

To save this customization in an XML file, I click Import/Export and select Export all customizations, and Access will save the customization with the extension .exportedUI.  You can now click Reset to remove the customization to the application ribbon.

3.  I need to do a few things to the XML file before I can use it in Ezy Access Ribbon Builder:

• Rename the file with the extension .XML.
• Edit the XML file to remove redundant text that is not recognised as well-formed XML by Access when you load a user-generated custom ribbon.

I use the free version of Expression Web 4 (download here) to prepare the XML file.

4. I open my XML file in Expression Web 4:

Click on the image to zoom

I then proceed as follows:

• Right-click in the document and click Reformat XML  to format the XML correctly
• Select and delete the first three lines of the reformatted document:

• Right-click in the document and click Verify well-formed XML...  to check that all the XML parses without error.

5. I save the XML file and exit Expression Web 4.

6. I open the XML file in Ezy Access RibbonX Builder to finesse the custom ribbon by using some different menu elements and the full range of native Office icons.

Update 25 June 2013: XML Notepad 2007 can also be used to work with the XML file.

Click on the image to zoom

7. After working on the XML in the Builder, I have a custom ribbon for my database:

Click on the image to zoom

NEW BETA 3 of Ezy Access RibbonX Builder & Editor for Access 2007/2010/2013

A new BETA 3 version of my Access Ribbon Builder and Editor for Microsoft© Access is now available for free download.  There is a new GUI, additional features, some bug fixes, and the app now supports Access 2007/2010/2013. The Builder is a fast and easy way to build and manage Access Custom Ribbons.

Download the fully functional unlimited BETA

Ezy Access RibbonX Builder is a basic yet sophisticated tool for building custom ribbonbars using XML files. The most common controls can be edited, and the essential attributes for custom controls can be edited. Native Office 2007 msoImages can be assigned to custom controls.

The ribbon being built is displayed as the active RibbonBar.  You use the treeview representation of the ribbon to navigate and  build the ribbon by adding, editing, copying, pasting, and deleting controls.

File commands:

• Load XML File... Loads an existing XML ribbon file.  The full file path is displayed in the XML File textbox.
• Refresh Reloads the current XML file and refreshes the displayed ribbon.
• Save As...  Saves the current XML file for the displayed ribbon as another file.

Ribbon commands:

• Edit Control Displays an Edit Control tab where you can edit the attributes of the control selected in the treeview.
• New Ribbon... Load a new ribbon stub and creates a new XML file for the ribbon.
• Reference Database... Assigns a database file to populate the onAction combobox for that ribbon control with a macro action from a macro named 'Ribbon'. The full file path is displayed in the Ref DB textbox.
• Ribbon Wizard... Select a ribbon template from the samples shipped with the Builder.

Other functions:

• Control objects combobox Select a control from this combobox to add to the displayed ribbon. Clicking the + button adds the selected control: if the + button is disabled then the selected control cannot be added to the current parent (container) control.  The buttons to the right of the + button from left-to-right delete, copy, or paste the control currently selected in the treeview.  The name of a copied control that has not yet been pasted is displayed in a label between the Paste and the Edit Control buttons.
• Lookup Office Image panel The functions in this panel allow you to assign images to custom ribbon controls during editing.  Place your mouse over a control in this panel for details of the function performed

Download the fully functional unlimited BETA

Office 2010 Implements User-Interface Ribbon Customization

Office 2010 will support what should have been in Office 2007: user-customization of RibbonBars across all Office client applications, using a paradigm similar to the right-click Customize... command available on CommandBars in pre-Office 2007 versions.  More at the Microsoft Office 2010 Engineering blog.

How to Build an Access 2007 Ribbon

Access MVP Garry Robinson has posted a useful article on building custom Access 2007 ribbons using the tool IDBE Ribbon Creator tool from http://www.ribboncreator.de/en/.

You should also checkout an article I wrote in December How Custom Access 2007 Ribbons Work.

I will take this opportunity to promote two free tools I have developed for reviewing custom ribbons that you can download from aadconsulting.com:

• Office 2007 Ribbon XML File Reviewer
• Access 2007 USysRibbons Reviewer Add-In
  

These tools providing advanced Office 2007 RibbonX review features. Open any Office 2007 Ribbon XML file in the XML File Reviewer to review an Office Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel.

The Access 2007 USysRibbons Add-In reviews and previews the custom Ribbons in the USysRibbons table of an Access 2007 database. Extensive reference resources are incorporated into the GUI of both products. Both tools require Access 2007.

The Access Ribbon Review in action (click on the image to enlarge):

How Custom Access 2007 Ribbons Work



The most challenging issue for Access users and developers when upgrading to Access 2007 is the radical shift from CommandBars to the RibbonBar paradigm.

In this outline I will introduce the operation of the Ribbon in Access 2007, provide some guidance on customisation, and point you to various tools and on-line resources to assist your efforts at Ribbon customization.

Getting Started:

1. Download and install my Access Ribbon Switchboard Database demo database.
   
   
2. Start Access 2007. Open the Access Ribbon Switchboard Database using the {SHIFT} key.
   
   
3. Click the Microsoft Office Button, click Access Options, and then click the Advanced tab.
   
   
4. In the General section, select the option Show add-in user interface errors, when debugging a RibbonBar.
   
   
5. Click OK to close the Access Options dialog box.
   
   
6. Right-click the Navigation pane. Point to Navigation Options, and then click Show System Objects. Click OK.
   
   Note: The Access system tables appear in the Navigation
   pane. In the Demo Database the system table, USysRibbons,
   each record contains the XML for each custom Ribbon in the
   database.
   
   
7. Click the Microsoft Office Button, and then click Access Options.
   
   
8. Click the Current Database tab, in the Navigation Pane and Ribbon and Toolbar Options sections, you will see that the following option settings hide the Database Window, the Navigation Pane, and the Full Menus, and set the custom ribbon, Main, as the StartUp Ribbon Bar:
   
   
   
   
   
9. Click OK to close the Access Options dialog.
   
   
10. Close the Demo Database.
    

11. Re-open the Demo Database without using the {SHIFT} key.
    

12. The Ribbon UI displays the Main Ribbon Custom Tab:
    
    Note: You use a Ribbon from the USysRibbons table to
    supply the UI for a specific form or report by setting the RibbonName
    property to the name of the Ribbon record in USysRibbons table.
    
    
    
    

Creating the XML Ribbon Definitions:

The ribbon definition is a simple XML file and can be easily created in any text editor, but if you use the free Microsoft Visual Web Developer 2005 Express Edition (VWD), you can get IntelliSense for your code. 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. You can download the file from http://officeblogs.net/UI/customUI.xsd. This enables VWD to provide type-ahead IntelliSense for correct ribbon xml:

The contents of the XML 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 attribute 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. These are used to logically group controls in the ribbon.
   
   
5. id - unique name of a control in the ribbon label - static text displayed with a control
   
   
6. button - similar to a command button. Use the onAction attribute to specify the name of a macro in the Ribbon macro in the Demo database which will be called when the button is clicked.
   
   
7. 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.
   
   
8. imageMSO - specifies the name of a built-in image control in Office 2007 that can be used as in icon in your ribbon. You can find the Image name or ControlID by hovering your mouse over a control image in the Customize the Quick Access Toolbar dialog, and reading the screentip for the control image. For example, the ControlID for the Form icon image is CreateForm:
   
   
   
   

Reviewing the Ribbon XML:

On aadconsulting.com I provide for free download two tools that will help you to debug the XML for your custom ribbon bars:

• Office 2007 Ribbon XML File Reviewer
• Access 2007 USysRibbons Reviewer Add-In
  

These freeware tools providing advanced Office 2007 Ribbon XML file review features. Open any Office 2007 Ribbon XML file in the XML File Reviewer to review an Office Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. The Access 2007 USysRibbons Add-In reviews and previews the custom Ribbons in the USysRibbons table of an Access 2007 database. Extensive reference resources are incorporated into the GUI's of both products. Both tools require Access 2007.

Update 9 June 2008: Check out the Office 2007 RibbonCreator WYSYWIG tool from http://www.ribboncreator.de/en/

More Information:

• Access 2007 Ribbon Attributes
• Access 2007 Ribbon Class
  
• Customizing the New Access UI
• Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Office RibbonX API- Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Ribbon Extensibility in Access 2007
• Office Custom UI Editor
• Customize the Office (2007) Ribbon UI for Developers (Part 1)
• Customize the Office (2007) Ribbon UI for Developers (Part 2)
• Customize the Office (2007) Ribbon UI for Developers (Part 3)
• Access Freak Access 2007 Tutorials
• AccessRibbon.com
• Ribbon Customizer

Office 2007 Ribbon XML File Reviewer Freeware

Click on the image to zoom in...

My new Access freeware tool provides advanced Office 2007 Ribbon XML file review features. Open your Ribbon XML file in the Reviewer to review the Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. Extensive reference resources are also incorporated into the GUI.

Download (Requires Access 2007)

You can also try the free BETA 2 of Access 2007 RibbonX Reviewer and Builder Add-In

BETA 2 of Custom RibbonX Reviewer for Access 2007

Click on the image to zoom in...

Download the just released free BETA 2 of my Access 2007 RibbonX Reviewer and Builder Add-In

BETA 2 provides advanced Access 2007 RibbonX review and build features, and is now deployed as an Access 2007 Add-In. You can create new Ribbons, copy/paste controls, and add new controls and delete controls on-the-fly. Extensive reference resources are incorporated into the GUI.

The next update will add the editing of control attributes.

Installation is now via a SetUp.exe file which includes the required DLL's and OCX's. The XML Preview Pane requires IE v 6.x or later. Make sure you read the readme.txt file.

NEW: Access 2003 to Access 2007 Interactive Command Reference Guide

Download a visual interactive reference guide to help you find Access 2003 commands in the new Access 2007 Ribbonbars.

You can also download the Access Ribbon mapping workbook, an Excel file that lists the commands on each tab of the Access 2007 Ribbon and the menu/toolbar location in previous versions of Access. Instructions on the first tab of the workbook provide tips for customizing, finding, and printing data.

Access 2007 Pain: RibbonBar

I was never a fan of the RibbonBar gui in Office 2007, but was ready to accept the need to work with it.

But at each step in the learning process , there is nothing but frustration. And I am not alone: read the comments to a post on the Access Team Blog.

A partial random list of annoyances:

1. There is no way to build or even review RibbonBars natively in Access 2007.
   
   
2. The XML for a RibbonBar does not parse in MS XML Notepad or by using the MSXML dlls - the XML needs to be modified to be read.
   
   
3. Using more than one period (.) in a row in a label breaks the XML.
   
   
4. There is no easy way to get the ImageID of a native image: you have to download an obscurely located Excel file.
   
   
5. The MS Custom Office UI Editor returns malformed XML errors by referring to the line and character number, but - you guessed it - the Editor screen has neither.
   
   
6. There is next to nothing on customizing the RibbonBar in the Access Help file. The only articles where you can get any coherent information have been written by third parties.
   
   
7. If you want to preview a native RibbonBar image in an Access form, you can't use the Access image control - you have to use the MS Forms image control, which is not fully supported in Access.

In one of the comments to the above-mentioned post, well-known Access writer Mike Groh, said:

"Without intellisense and without an Object Browser view of the ribbon hierarchy, it's very, very difficult to make headway on ribbon customization."

A correction: The RibbonBar gui does not have an object model, so the hierarchy has to be viewed in some other way.

On 20 Sep I posted my Custom RibbonX Reviewer BETA, for free download. The Reviewer loads a custom RibbonBar into a treeview to show the full hierarchy, and all attributes and values for each item, as well as displaying the actual RibbonBar. There are also extensive reference resources incorporated into the GUI. The next stage in the project is to deploy the app as an add-in that will work inside the users' ACCDB, and provide in-place editing of RibbonBars using a pseudo-object model.

I sent an email to the Access Team Blog on this app, in the hope I would get some support or feedback. The email has been ignored.

Office 2000 Ribbon Customizer

From the What's New in Access 2007 Blog:

For those of you who have been struggling a little with Ribbon customization in Access 2007 and miss the old world of CommandBars where you could design the command experience directly through the UI, Patrick Schmid has a cool add-in that allows you to make graphical design changes to your Ribbons from right within Office. It works with all Office apps that have the Ribbon.

His tool is free to try, and he's also currently got a beta program going. You can pick up the trial here: http://pschmid.net/office2007/ribboncustomizer/download.php, and join the Beta here: http://pschmid.net/office2007/ribboncustomizer/betaprogram.php.