Monday, June 24, 2013

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

Wednesday, June 19, 2013

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