update ribbon label and image based on values in a table-CodePudding

I have a custom ribbon, I'd like to make one of the buttons dynamic based on values in a table so, when the form is closed, I'd like for the ribbon button and label to change based on changes made in the form (if there were any)

Here's my VBA code just for that button. And I created 2 separate buttons with different images and labels in the USysRibbonImages table. I have mine setup a tad different, that table also stores button names, macro names and labels, besides the images. I did it that way since each image record can only be tied to one button anyway, so, figured I might as well make my images into buttons in the same table. And this process is automated somewhat. I'm explaining in case someone wonders why the code is a little different from what you usually see for ribbons.

But it works fine for static ribbons, it's once I want to make them a little dynamic is where I have issues

Public Sub fnGetRibbonImages(control As IRibbonControl, ByRef image)

Dim attach As Attachment
DoCmd.OpenForm "fZRibbonImages", acNormal, , , , acHidden
    
    If control.ID = "btnServicesShippingWeightBucketsCollections" Or control.ID = "btnServicesShippingWeightBucketsCollectionsRed" Then
        'regular
         If dCount("MissingServicesShippingWeightBucketsID", "MissingServicesShippingWeightBuckets") > 0 Then
            Forms("fZRibbonImages").Filter = "([USysRibbonImages].[ButtonName]='btnServicesShippingWeightBucketsCollections')"
        Else
        'red
            Forms("fZRibbonImages").Filter = "([USysRibbonImages].[ButtonName]='btnServicesShippingWeightBucketsCollectionsRed')"
        End If
            Forms("fZRibbonImages").FilterOn = True
            Set attach = Forms("fZRibbonImages").Controls.Item("Images")
            Set image = attach.PictureDisp()
    End If
 
DoCmd.Close acForm, "fZRibbonImages", acSaveYes

End Sub

Public Sub GetRibbonLabel(ByVal control As Office.IRibbonControl, ByRef returnedVal)

    If control.ID = "btnServicesShippingWeightBucketsCollections" Or control.ID = "btnServicesShippingWeightBucketsCollectionsRed" Then
        If dCount("MissingServicesShippingWeightBucketsID", "MissingServicesShippingWeightBuckets") > 0 Then
            returnedVal = "FIX!!!!!"
        Else
            returnedVal = "Buckets"
        End If
    End If
  End Sub

And here's my ribbon xml (I removed a bunch of buttons, the code is the same for all)

<?xml version="1.0" encoding="utf-8"?>
<customUI
    xmlns="http://schemas.microsoft.com/office/2006/01/customui" onl oad="OnLoadRibbon" loadImage="OnLoadImage">
    <ribbon startFromScratch="true">
        <tabs>
            <tab id="tabDefault" label="App Options">
                <group id="grpMappings" label="Mappings">
                    <button id="btnZoneXWalk" label="Zone Xwalk" onAction="mZoneXWalk" getImage="fnGetRibbonImages" size="large"/>
                    <button id="btnServicesShippingWeightBucketsCollections" label="Shipping Weight Buckets" onAction="mServicesShippingWeightBucketsCollections" getImage="fnGetRibbonImages" size="large"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

I tried putting this with the CLOSE button for the form

sbRefreshRibbon
MyRibbon.Invalidate

And here's the code for the sbRefreshRibbon sub

Public Sub sbRefreshRibbon()
On Error GoTo RestartApp
MyRibbon.Invalidate
On Error GoTo 0
Exit Sub
RestartApp:
  MsgBox "Please restart Application for Ribbon changes to take effect", _
    vbCritical, "Ribbon Refresh Failed"
End Sub

No luck, except after a few changes (don't remember exactly what I did, I've been at this for a while) I got an error that said to restart to reload the ribbon

CodePudding user response：

Instead of the label attribute for the ribbon controls you need to use the getLabel one with a callback which should have the following signature:

C#: string GetLabel(IRibbonControl control)

VBA: Sub GetLabel(control As IRibbonControl, ByRef label)

C  : HRESULT GetLabel([in] IRibbonControl *pControl, [out, retval] BSTR *pbstrLabel)

Visual Basic: Function GetLabel(control As IRibbonControl) As String

So, in your ribbon XML file you need to replace the label attribute:

<group id="grpMappings" label="Mappings">
    <button id="btnZoneXWalk" label="Zone Xwalk" onAction="mZoneXWalk" getImage="fnGetRibbonImages" size="large"/>
    <button id="btnServicesShippingWeightBucketsCollections" getLabel="GetRibbonLabel" onAction="mServicesShippingWeightBucketsCollections" getImage="fnGetRibbonImages" size="large"/>
</group>

Read more about the Fluent UI (aka Ribbon UI) in the following articles: