Dynamically changes ribbon's button label Excel

Question

I am using the following piece of XML code to create a custom Ribbon for an Excel add-in.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="ComdinheiroTab" label="COMDINHEIRO">
                <group id="ComdinheiroButtons" label="Comdinheiro">

                    <button id="Login" getLabel="getLabelLogin" image="Login" size="large" onAction="OnActionLogin"/>

                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

I am using the following VBA code to set a label for the button login:

Sub getLabelLogin(control As IRibbonControl, ByRef returnedVal)
 if loggedIn = true then
    returnedVal = "Logged"
 else 
    returnedVal = "Disconected"
 end if
End Sub

The label's name changes successfully according to the variable loggedIn's value when the ribbon is loaded. However I wish I could change the label's values during the execution of my program. Is it possible to call getLabel event using a VB code? Is there anyway to refresh my ribbon so this event would be called again?

Answer 1

Yes, it's possible to run "get" callbacks later on. In order to do so, you need to create a module- or global-level variable to hold the "ribbon UI" object. That object has two useful methods: Invalidate and InvalidateControl. The first triggers all the "get" callbacks in the Ribbon XML. The second triggers the callbacks only for the specified control.

Your ribbon ui must be assigned to this object when the Ribbon loads. In order for this to happen, you need the attribute onLoadin the customUI tag of your Ribbon XML and its callback in your VBA.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ribbonLoaded">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="ComdinheiroTab" label="COMDINHEIRO">
                <group id="ComdinheiroButtons" label="Comdinheiro">

                    <button id="Login" getLabel="getLabelLogin" image="Login" size="large" onAction="OnActionLogin"/>

                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

In the VBA sample code, below, UpdateTheLabel is called from "the execution of my program" mentioned in the question. This procedure need not be connected to the RibbonXML in any way.

UpdateTheLabels action, however, is connected to the Ribbon: by calling InvalidateControl it causes a Ribbon control ("Login", in this case) to re-evaluate all its dynamic ("get") call-backs, such as getLabelLogin (which is in the Ribbon XML).

Dim ribbonUI as IRibbonUI

Sub ribbonLoaded(ribbon as IRibbonUI)
  Set ribbonUI = ribbon
End Sub

Sub UpdateTheLabel
  ribbonUI.InvalidateControl("Login")
End Sub

Sub getLabelLogin(control As IRibbonControl, ByRef returnedVal)
 if loggedIn = true then
    returnedVal = "Logged"
 else 
    returnedVal = "Disconected"
 end if
End Sub

It doesn't matter what procedure calls InvalidateControl as long as the procedure has access to the ribbonUI object.

More on this can be found in the MSDN article https://msdn.microsoft.com/en-us/library/aa338202(v=office.12)#OfficeCustomizingRibbonUIforDevelopers_Dynamically