Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    Joined
    Oct 2021
    Posts
    8
    Location

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

    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.
    Attached Images Attached Images

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,026
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Oct 2021
    Posts
    8
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •