Adding Tabs and Controls to the Office Fluent Ribbon in Access 2007

Microsoft Office Access 2007 lets you customize the ribbon in your application. The ribbon provides a new user model for exposing commands, and it provides easier, more discoverable navigation. You can create a custom ribbon for an Office Access 2007 database by using only ribbon extensibility markup XML and macros.

This video demonstrates the four steps to customize the ribbon for a database application:

1. Create XML markup in the editor of your choice.

2. Load XML into the database.

3. Set database, form, and report properties to show customizations.

4. Use macros or VBA to handle simple actions, such as button clicks.

Creating XML Markup

The following XML samples demonstrate the basic steps to create the XML markup that will display the custom ribbon buttons in an Access database. To see how to add these to your database, watch the video.

The simple XML below sets the startFromScratch attribute to True, which hides the default ribbon.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon startFromScratch="true"/>
</customUI>

Next, extend the XML to add a new tab to hold your custom buttons.

<customUI xmlns="http://schemas.microsoft.com
/office/2006/01/customui">
 <ribbon startFromScratch="true">
  <tabs>
    <tab id="tabMain" label="Home">
       <group id="grpProjects" label="Projects"/>
    </tab>
  </tabs>
 </ribbon>
</customUI>

Finally, add the buttons by adding the emphasized code show below.

<customUI xmlns="http://schemas.microsoft.com
/office/2006/01/customui">
 <ribbon startFromScratch="true">
  <tabs>
   <tab id="tabMain" label="Home">
    <group id="grpProjects" label="Projects">
     <button id="cmdHome" label="Home" 
       onAction=
     "Ribbon.MarketingProjectsHome" size="large" 
       imageMso="OpenStartPage" supertip=
       "Return to the 
        Marketing Projects home page. 
        Hold down the right shift key to open the 
        database with full menus."/>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Let's look now at the details of the XML.

• The imageMso attribute refers to a ribbon image name that is pulled from the 2007 Office System Icon Gallery.

• The onAction attribute invokes a macro named Ribbon.MarketingProjectsHome that creates a macro object named ribbon. This object contains an action called MarketingProjectsHome that calls the OpenForm action to open the Marketing Projects Home form.

Using VBA to Handle More Advanced Actions

You can do many powerful actions using macros, but you may need to use more advanced tasks by creating VBA functions. For example, you need to write code for the following scenarios:

• Loading XML from a remote location.

• Implementing a callback.

• Loading custom images.

Loading Remote Code

This code creates a Recordset from any table that contains the word "Ribbons" in its name. Then it calls the LoadCustomUI method to load the ribbons, to make them available to the database. And finally, it closes the Recordset and the reference to the DAO.Database object.

Option Compare Database

Function LoadRibbons()
Dim i As Integer
Dim db As DAO.Database
Set db = Application.CurrentDb
    
For i = 0 To (db.TableDefs.Count - 1)
    If (InStr(1, db.TableDefs(i).Name, "Ribbons")) Then
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset(db.TableDefs(i).Name)
        rs.MoveFirst

        While Not rs.EOF
            Application.LoadCustomUI rs("RibbonName").Value, 
            rs("RibbonXml").Value
            rs.MoveNext
        Wend
        rs.Close
        Set rs = Nothing
    End If
Next i

db.Close
Set db = Nothing
End Function

Implementing Callbacks

In the following example, the group element is declared with the label attribute. The label for this group is static, which means it is set when the group is created:

<group id="Navigation" label="Navigation">

In practice, you may want to assign the label dynamically. In the following example, the group element is declared with the getLabel attribute. When this group is created, the ribbon calls the getLabel function to get the value for the label.

<group id="Navigation" getLabel="onGetLabel">

The following example shows the VBA function that is called when the ribbon evaluates the XML in the previous example.

Function onGetLabel(control As IRibbonControl, ByRef label)          
    Select Case control.ID
    Case "Navigation":
        Label = "dynamic label text!"
    End Select
End Function

Loading Custom Images

The following VBA examples show how to implement the getImage callback function to load images from an attachment field in the current database. This function uses a form to load images.

In this example, all the images for the application are stored in the same record.

Option Compare Database
Option Explicit
Dim formOpened As Boolean
Dim attach As Attachment

Function GetImage(imageId As String, ByRef image)
    If (Not formOpened) Then
        DoCmd.OpenForm "USysRibbonImages", acNormal, , , 
        acFormReadOnly, acHidden
        Set attach = Forms("USysRibbonImages").Controls
        ("Images") formOpened = True
    End If
    Set image = attach.PictureDisp(imageId)
End Function 

In the previous function, the USysRibbonImages opens as read-only and in hidden mode. This indicates that it is not in the UI. The images are retrieved from the Images control. The image whose name corresponds to the imageId that is requested by the ribbon is returned.

In the following example, images for the application are stored in different records. If a record contains more than one image in the Images field, the first image is retrieved by default.

Function GetImage(imageId As String, ByRef image)
    Static frmRibbonImages As Form_USysRibbonImages
    Static rsForm As DAO.Recordset2
    Dim rsAttachments As DAO.Recordset2
    If frmRibbonImages Is Nothing Then
        DoCmd.OpenForm "USysRibbonImages", WindowMode:=acHidden
        Set frmRibbonImages = Forms("USysRibbonImages")
        Set rsForm = frmRibbonImages.Recordset
    End If
    
    rsForm.FindFirst "ControlID='" & control.ID & "'"
    If rsForm.NoMatch Then
        ' No image found
        Set image = Nothing
    Else
        Set image = frmRibbonImages.RibbonImages.PictureDisp
    End If
End Function

Access provides flexibility in customizing the ribbon UI. For example, you can store customization markup in a table, embedded in a Visual Basic for Applications (VBA) procedure, stored in another Access database, or linked to from a Microsoft Office Excel 2007 worksheet. You can also specify a custom UI for the application as a whole or for specific forms and reports.

You create XML to change the ribbon UI. You can create customization files in any text editor. All applications that include the ribbon use the same extensibility model, so you can reuse the same ribbon extensibility XML with a minimum of adjustments. For example, you can reuse the custom XML you create for the 2007 Microsoft Office release programs, such as Microsoft Office Word, Microsoft Office Excel, Microsoft Office PowerPoint, or Access, and for Microsoft Office Outlook 2007.

For more information about ribbon architecture and the specification of the XML markup, see Customizing the Office (2007) Ribbon User Interface for Developers (Part 1 of 3).

Watch the Video

Length: 00:07:22| Size: 7.4 MB | Type: WMV file

• Customizing the Office Fluent User Interface in Access 2007

• Customizing the Office (2007) Ribbon User Interface for Developers (Part 1 of 3)

• 2007 Office System: XML Schema Reference

• 2007 Office System Lists of Control IDs

• 2007 Office System Add-In: Icons Gallery