Microsoft Access Ribbon Customization Part 1 (article date: Apr-5-2009)

This is part 1 of a 4 part series on how to customize the Access 2007 ribbon and other tips related to the quick launch toolbar and navigation pane. In this example we show the basics of doing your first ribbon, how to get started and other references to learn more. In parts 2 thru 4 of this series we add functionality to the ribbon and database.

Getting Started

Download the code for this article
Open the Ribbon1.accdb database in Microsoft Access 2007
Click the Microsoft Office button (the button at the top left), choose Access Options…, Current Database, Navigation Options…, and click Show System Objects

This will make it so you can see the UsysRibbons table (which is used to hold the XML which drives any custom ribbons you want in your database)

Another good thing to do is to Trust the location you store the database, so you won’t have to choose enable this content all the time. Access Options…, Trust Center, Trust Center Settings…, Trusted Locations, Add New Location
Because you will need it once you start tweaking XML on your own, you should goto Access Options…, Advanced, Scroll down to the General section and check “Show add-in user interface errors”.
You could of also set the ribbon to use at startup by going to: Access Options, Current Database and choosing the ribbon Name from the dropdown. You will see Home as a choice because the UsysRibbons table had XML that when the database was opened caused Access to create a ribbon called “home”. This is not needed for this example, just pointing it out.
The Ribbon1.accdb database has the following objects:
- table UsysRibbons (used to store the XML to create the Ribbon(s))
- form frmMain (just an example form that has the Ribbon property set to “Home” which is the name of our custom ribbon)
- module basRibbonCallbacks (a module used to hold the code for the callbacks used in the ribbon “Home”)

As you can see our custom ribbon looks like this:

The ribbon has one tab (home) with 5 groups (Data, Search, Tools, Options, Info) and each groups has some buttons, labels or dropdown controls. The buttons when clicked show a messagebox and the dropdown has choices defined and when one is selected it also shows a message box.

Now the tough part, how does this all work? Here’s where I recommend you looking at the articles on Ribbon customization and other references needed to gain a full understanding. I will attempt to guide you through this so you can learn how this simple example works and after that we can expand on this learning.

Step 1: download the file “2007 Office System Add-In: Icons Gallery” which is an Excel 2007 worksheet that makes it easy to see all the possible images you can use. Try this link, if it doesn’t work I leave you to search for this file:

Microsoft downloads related links

Step 2: download the “2007 Office System Document: List of Control IDs” which is a series of Excel spreadsheets listing the controlids you could possibly use for built-in functionality.

Look at the spreadsheet in step #1 (to see all the available icons).

Now let’s dive into the XML to see how it works:

---------------------------------------------------------------------------
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">
    <ribbon startFromScratch="true">
        <tabs>
            <tab id="tabHome" label="Home">
               <group id="grpData" label="Data"  visible="true">
                    <button id="CustomerButton" label="Customers" 
			imageMso="CreateTableTemplatesGallery" 
			size="large" 
			onAction="OnActionButton" /> 
		    <button id="EmployeeButton" label="Employees" 
			imageMso="AddOrRemoveAttendees" 
			size="large" 
			onAction="OnActionButton" />
               </group>
               <group id="grpSearch" label="Search"  visible="true">
		    <button id="FindAvailableTimeButton" label="Find Available Time"
 			imageMso="SlideShowRehearseTimings" size="large" 
			onAction="OnActionButton" />
		    <button id="SearchForAppointmentButton" label="Search For Appointment" 
			imageMso="ViewAppointmentInCalendar" 
			size="large" onAction="OnActionButton" />
                </group>
               <group id="grpTools" label="Tools"  visible="true">
		    <button id="ReportButton" label="Reports" 
			imageMso="PropertySheet" 
			size="large" 
			onAction="OnActionButton" />
		    <button id="EmailButton" label="Email" 
			imageMso="FileSendMenu" 
			size="large" 
			onAction="OnActionButton" />
		    <button id="LetterButton" label="Letters" 
			imageMso="FileSaveAsWord97_2003" 
			size="large"
			onAction="OnActionButton" />
                </group>
               <group id="grpOptions" label="Options"  visible="true">
		    <button id="OptionsButton" label="Calendar Options" 
			imageMso="OmsAccountSetup" size="normal"
			onAction="OnActionButton" />
		    <dropDown id="TimescaleDropDown" label="Timescale" 
			getItemCount="OnGetItemCount"
			getItemLabel="OnGetItemLabel"
			getSelectedItemIndex="GetSelectedItemIndexDropDown" 
			imageMso="StartAfterPrevious"
			onAction="OnActionDropdown" />
		    <labelControl id="DateLabel" getLabel="Getlabel" />
                </group>
                <group id="grpInfo" label="Info">
          	    <button id="InfoButton" size="large" label="Info" 
		imageMso="Info" onAction="OnActionButton"/>
        	</group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
---------------------------------------------------------------------------

General:

Look through the XML (stored in UsysRibbons). Hopefully you can understand how tabs and groups are specified.

To add the customer button:

---------------------------------------------------------------------------
<button id="CustomerButton" label="Customers" imageMso="CreateTableTemplatesGallery"
size="large" onAction="OnActionButton" />
---------------------------------------------------------------------------

The id is “CustomerButton” this can be any string

The label is “Customers” and is hardcoded in this example.
The imageMso was found by picking an image (using the step 1 spreadsheet)
The size is “large”, “normal” being the other choice
The onAction is the callback (or the function that is called when the button is clicked)

If you look at the OnActionButton subroutine in module basRibbonCallbacks you can see that this same subroutine is used for all the buttons on the ribbon. Callback subroutines have a specific syntax that must be followed. You could easily change the logic of OnActionButton so that when a button is clicked something else happens such as a new form opening etc…

If you now look at the code in more detail you will see other callback routines:

GetLabel is used for label controls or anywhere you want to assign a value to a label in code
OnGetItemCount is used to determine how many items in a dropdown control
OnGetItemLabel is used to determine the values used in the dropdown
GetSelectedItemIndexDropdown is used to set the value of the dropdown
OnActionDropdown is used when the dropdown value is changed by the user
OnRibbonLoad is used when the ribbon is loading (not needed much in this simple example but you will usually have this subroutine in the mix).

Try changing the image on a button or adding a new button into the ribbon. After you change the XML you need to close the application and reload it for change to take effect.

Hopefully this got you a little more comfortable with Ribbons and ribbon customization, now look at the other references:

www.ribboncreator.de/en/
Msdn Ribbon Portal
Books on Access 2007 or RibbonX

If you liked this article feel free to send us a note or donate a few dollars using paypal. Click here to download the complete code example..