How to change the label value on Excel 2007 ribbon programatically when Worksheet is opened

Question

On Excel 2007 ribbon, I add a new group with a button. I need to be able to change the label text from "ABC_Execute" to "something else" based on a condition checked when the sheet is first opened (onload event for example) - How to do that in VBA?

Example code that is used to customize the ribbon:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="ABC" insertAfterMso="TabHome">
                <group id="customGroup" label="ABC Tools">
                    <button id="customButton1" label="ABC_Execute" size="large" onAction="Begin" imageMso="Bold" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Thx.

Rory Rory · Accepted Answer · 2014-10-21T07:35:57

You need to add a getLabel callback to your CustomUI:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rx_rib_on_load">
    <ribbon>
        <tabs>
            <tab id="customTab" label="ABC" insertAfterMso="TabHome">
                <group id="customGroup" label="ABC Tools">
                    <button id="customButton1" getLabel="rx_getLabel" size="large" onAction="Begin" imageMso="Bold" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

then in a workbook module:

Sub rx_getLabel(control As IRibbonControl, ByRef returnedVal)
    returnedVal = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
End Sub

for example. If you need to change the value subsequently, you'll want an onLoad callback so that you can invalidate the control/ribbon when required.

How to change the label value on Excel 2007 ribbon programatically when Worksheet is opened

1 Answers