Custom Ribbon onAction syntax question

Question

I followed the directions here to create a custom ribbon for an Access application. But none of the buttons worked! I kept getting an error that stated Access couldn't find the function or macro, even though it was public and in a standard module.

Eventually I discovered that it would work if I used the following syntax:

onAction="=fncMyFunction('string argument', 1234)"

fncMyFunction receives the manually typed in arguments, but not the ribbon object.

In Word for another project, I created a custom Ribbon by opening the document up as a .ZIP file, adding the XML in the appropriate place, and adding a reference to it. Relevant directions somewhere in this novel here.

In Word, I was able to have everything work the way I expected it to with the following syntax:

onAction="fncMyFunction"

In Word, fncMyFunction has a ribbon object passed to it when the button is clicked.

What's the deal here? Why the different syntax? And is one way or the other "wrong?"

Solution

You should use the ribbon element's tag property to store some values you want to pass to your action.

For instance, say you have a simple ribbon containing a few buttons:

• the first button uses a generic action ribbonOpenForm that opens a form FormDashBoardFinance when clicked.
• the second button uses a generic action ribbonDoAction that execute the LogOff("bye") VBA function (not a Sub!) that, for instance, displays a message to the user and logs off.
• the last one duplicates the behaviour that you wanted for your fncMyFunction().

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
          onLoad="ribbonLoad" loadImage="ribbonLoadImage">
   <ribbon startFromScratch="false">
        <tabs>
         <tab id="Home" label="Home">
                   <group id="gpDash" label="Dashboards">
                        <button id="btHomeFinance"
                                label="Finance"
                                imageMso="BlogHomePage"
                                onAction="ribbonOpenForm" 
                                tag="FormDashBoardFinance"/>
                        <button id="btLogOff"
                                label="Log Off"
                                imageMso="DatabasePermissionsMenu"
                                onAction="ribbonDoAction" 
                                tag="LogOff('bye')"/>
                        <button id="btMyFunc"
                                label="My Function"
                                imageMso="AppointmentColorDialog"
                                onAction="fncMyFunction" 
                                tag="'a string argument', 1234"/>
                   </group>
             </tab>
        </tabs>
   </ribbon>
</customUI>

The VBA to manage the ribbon would be in a module:

Option Compare Database
Option Explicit

' We keep a reference to the loaded Ribbon
Private ribbon As IRibbonUI

'-----------------------------------------------------------------------------
' Save a reference to the Ribbon
' This is called from the ribbon's OnLoad event
'-----------------------------------------------------------------------------
Public Sub ribbonLoad(rb As IRibbonUI)
    Set ribbon = rb
End Sub

'-----------------------------------------------------------------------------
' Open the Form specified by the ribbon control's Tag.
'-----------------------------------------------------------------------------
Public Sub ribbonOpenForm(control As IRibbonControl)
    DoCmd.OpenForm control.tag, acNormal
End Sub

'-----------------------------------------------------------------------------
' Perform the action specified by the ribbon control's Tag
' Use single quotes to delimit strings, they will be expanded.
' The action to be performed must be defined as a public Function!
'-----------------------------------------------------------------------------
Public Sub ribbonDoAction(control As IRibbonControl)
    Dim action As String
    action = Replace(control.Tag,"'","""")
    Eval action
End Sub

'-----------------------------------------------------------------------------
' fncMyFunction example implementation
' Use single quotes to delimit strings, they will be expanded.
'-----------------------------------------------------------------------------
Public Sub fncMyFunction(control As IRibbonControl)
    ' Split the string to separate the paramaters in the Tag
    Dim params As Variant
    params = Split(control.Tag, ",")
    ' Now we can assign each parameter
    Dim myString As String
    Dim myInt As Integer
    myString = Replace(Trim(params(0)),"'","") ' remove single quotes
    myInt = CInt(Trim$(params(1)))             ' We're expecting an Integer
    ' ... do something with the params ...
    Debug.Print myString  ' Will print: a string argument
    Debug.Print myInt * 2 ' Will print: 2468
End Sub

An excellent resource for the Access Ribbon is Avenius Gunter's Access 2010 Ribbon site