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?"

Answer 1

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