RibbonX - Setting pressed state of Checkbox control at runtime

[Paul_Hossler]

Take a look at this example

I just have 4 sheets, but the pressed togglebutton is saved in a collection and reset when you reenter the sheet. I used the Workbook events

The numbers in A1 don't mean anything -- just my test markers

Option Explicit


Private Sub Workbook_Open()
    Dim ws As Worksheet
    
    Set collPressed = New Collection
    
    'set all to first one
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            collPressed.Add "MenuToggleButton1", ws.Name
        End If
    Next
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    sPressed = collPressed.Item(Sh.Name)
    oRibbon.Invalidate
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    collPressed.Remove Sh.Name
    collPressed.Add sPressed, Sh.Name
End Sub