Change appearance of custom buttons on Excel ribbon based on cell status or contents?

[pplanch]

I've got some buttons in an add-in on a custom ribbon (with custom icons).

Is it possible to change the appearance of such a button based on the contents or state of a cell in a workbook ? (either through the customUI XML or through VBA)

Example from the built-in buttons of Excel that I would like to mimic : When selecting a cell that is "bottom aligned", on the Home -> Alignment button group, the button for "bottom alignment" is highlighted. When selecting a cell that is "top aligned", on the same button group the button for "top alignment" is highlighted.

See screenshots in attachments for what I would like to achieve with my own buttons.

[Paul_Hossler]

Q1 - are Top/Bottom aligned just an example?

Q2 - I don't think there's any events that are triggered on a cell format change, only of a Selection change, Value change, or a Calculate

XML using getImage with a callback to show an MSO image, and the callbacks

there's two WS events on SHeet1 also

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OnRibbonLoad" >
<ribbon>
<tabs>
<tab id="CustomTab" label="My Tab">


    <group id="CustomGroup4"     label="From Worksheet" insertBeforeMso="GroupClipboard">
        <button id="buttonOne"   label="Button1" size="large" onAction = "ClickButtonCard" getImage="Card"/>
        <button id="buttonTwo"   label="Button2" size="large" onAction = "ClickButtonCard" getImage="Card"/>
        <button id="buttonThree" label="Button3" size="large" onAction = "ClickButtonCard" getImage="Card"/>
    </group>
</tab>
</tabs>
</ribbon>
</customUI>

Option Explicit

Public oRibbon As IRibbonUI


'Callback for customUI.onLoad
Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set oRibbon = ribbon
End Sub


'Callback for buttonOne onAction
Sub ClickButtonCard(control As IRibbonControl)
    MsgBox control.id
End Sub


'Callback for buttonOne getImage
Sub Card(control As IRibbonControl, ByRef returnedVal)
    
    With Worksheets("Sheet1")
        Select Case control.id
            Case "buttonOne"
                Select Case .Cells(2, 1)
                    Case 1
                        returnedVal = "Spade"
                    Case 2
                        returnedVal = "Club"
                    Case 3
                        returnedVal = "Heart"
                    Case Else
                        returnedVal = "Diamond"
                End Select
            
            Case "buttonTwo"
                Select Case .Cells(2, 2)
                    Case 1
                        returnedVal = "Spade"
                    Case 2
                        returnedVal = "Club"
                    Case 3
                        returnedVal = "Heart"
                    Case Else
                        returnedVal = "Diamond"
                End Select
            
        
            Case "buttonThree"
                Select Case .Cells(2, 3)
                    Case 1
                        returnedVal = "Spade"
                    Case 2
                        returnedVal = "Club"
                    Case 3
                        returnedVal = "Heart"
                    Case Else
                        returnedVal = "Diamond"
                End Select
        End Select
    End With
    
    oRibbon.InvalidateControl control.id
End Sub

[pplanch]

Paul, thanks. I’ll have a look at your examples.

The two screenshots I sent are examples of standard Excel behaviour I’m hoping to mimic, similar to e.g. Bold font button on the format ribbon group. Just as an FYI on your first question.