Excel Ribbon comboBox: always display current selection

Question

This is a follow-up question to this one:

Excel Ribbon comboBox: always display current selection and remember selection before saving

I'm trying to set up a comboBox to select sheets.

The "original" code has two issues:

1. when selecting a sheet, the comboBox does not refresh.
2. upon saving and reopening, the comboBox defaults to the first listed value.

FaneDuru explains all the necessary steps and pieces of code you need to make this work.

Unfortunately, I must be missing something.

The second issue is solved, the workbook now opens with the last selection. The first issue remains: when clicking on a sheets' tab, the comboBox does not refresh yet. I guess I placed the variables used to write/read the Registry in the wrong place? Should they be in a separate module? And is it correct, that RibUI.InvalidateControl "ComboBox001" is in two places? In "ThisWorkbook" as well as in the Callbacks-module?

Thanks!

--XML
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
    <ribbon>
        <tabs>
            <tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">                  
                <group id="GroupDemo2" 
                label="SelectSheet"
                imageMso="AddInManager">
                    <comboBox id="ComboBox001"
                    label="comboBox001"
                    getText="ComboBox001_GetText"
                    onChange="ComboBox001_OnChange">
                        <item id="ItemOne"
                        label="One"/>
                        <item id="ItemTwo"
                        label="Two"/>
                        <item id="ItemThree"
                        label="Three"/>
                    </comboBox>
                </group>     
            </tab>
        </tabs>
    </ribbon>
</customUI>

--Callback VBA in Module "RibbonCallbacks"
Option Explicit
Public RibUI As IRibbonUI
Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"

Sub LoadRibbon(Ribbon As IRibbonUI)
    Set RibUI = Ribbon
    RibUI.InvalidateControl "ComboBox001"
End Sub

'Callback for ComboBox001 onChange
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Select Case id
        Case "One"
            ThisWorkbook.Sheets("Sheet1").Select
        Case "Two"
            ThisWorkbook.Sheets("Sheet2").Select
        Case "Three"
            ThisWorkbook.Sheets("Sheet3").Select
    End Select
    RibUI.InvalidateControl "ComboBox001"
    SaveSetting myApp, mySett, myVal, id
End Sub

'Callback for ComboBox001 getText
Sub ComboBox001_getText(control As IRibbonControl, ByRef returnedVal)
  Dim comboVal As String
    comboVal = GetSetting(myApp, mySett, myVal, "No Value") 'read it from Registry
    If comboVal <> "No Value" Then
      returnedVal = comboVal
    End If
End Sub

--VBA in "ThisWorkbook"
Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    RibUI.InvalidateControl "ComboBox001"
End Sub

Answer 1

• Your code is close to finish. The only missing part is SaveSetting in Workbook_SheetActivate event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim idx As String
    Select Case Sh.Name
    Case "Sheet1"
        idx = "One"
    Case "Sheet2"
        idx = "Two"
    Case "Sheet3"
        idx = "Three"
    End Select
    SaveSetting myApp, mySett, myVal, idx
    RibUI.InvalidateControl "ComboBox001"
End Sub