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

Question

How can I set up a comboBox, so that it always updates/refreshes its display and always shows the current selection?

A. In this example, I can select sheets. If I select via the comboBox, everything seems fine. But as soon as I select a sheet by clicking its tab, the comboBox does not refresh. For example, I select "Sheet1" via the comboBox. Then I select "Sheet3" via its tab. The box stills shows "Sheet1".

B. Also, when closing and opening the workbook, the selection defaults to the first position in the comboBox. How can I tell the comboBox, to stay in its position, when saving and closing?

-- XML
<group id="GroupDemo2" 
    label="SelectSheet"
    imageMso="AddInManager">    
    <comboBox id="ComboBox001"
        label="comboBox001"
        onChange="ComboBox001_OnChange">
        <item id="ItemOne"
            label="One"/>
        <item id="ItemTwo"
            label="Two"/>
        <item id="ItemThree"
            label="Three"/>
    </comboBox>
</group>   

-- Callback VBA Code 
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Select Case id
        Case "One"
            Sheets("Sheet1").Select
        Case "Two"
            Sheets("Sheet2").Select
        Case "Three"
            Sheets("Sheet3").Select
    End Select
End Sub

Thanks!

Answer 1

You did not answer my clarification question... So, I will consider that he "Workbook" you are referring to in terms of correlation between its active sheet and the Ribbon combo box, is the one keeping the XML shown code, not an add-in.

There are two issue in your question.

1. The first one can be easily solved as suggested in the first comment, respectively, use Invalidate control. I will show in the code I intend to post the event modified code.

2. The second one, related to correlation of the workbook selected sheet and Ribbon combo box value, is a little more complicated, but not very... Basically, you need to write/read in Registry the changed combo value and adapt a little the XML and existing VBA code.

A. Please, adapt the XML code (inserting a new line starting with getText) in the next way:

      <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>       

getText is triggered by Invalidate control.

B. Declare on top of a standard module (in the declarations area) the next variables, used to write/read the Registry:

Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"

C. OnChange event must be modified in the next way (to Invalidate control and write in Registry the last combo value):

Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Select Case id
        Case "One"
            Sheets("Sheet1").Select 'it would be good to use here ThisWorkbook.Sheets("").
                                    'otherwise, it will consider the active sheet of any open workbook
        Case "Two"
            Sheets("Sheet2").Select
        Case "Three"
            Sheets("Sheet3").Select
    End Select
    myRibbon.InvalidateControl "ComboBox001"
    SaveSetting myApp, mySett, myVal, id
End Sub

D. In order to be able to change the combo value with the one memorized in Registry, you also need to copy the next sub:

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

In order to do that, it must be triggered! This is done running Invalidate control. This must be done when the workbook keeping the XML code is open. I suppose that you have on top of the XML something like:

<customUI onLoad="RibbonLoaded_MyWB" xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 

onLoad calls the sub RibbonLoaded_MyWB (which, probably, in your case is named diferently). Basically, such a Sub should look as:

Sub RibbonLoaded_MyAddin(ribbon As IRibbonUI)
   Set myRibbon = ribbon
   
   myRibbon.InvalidateControl "ComboBox001" 'to trigger getText...
End Sub

Of course, you need to have declared on top of a standard module myRibbon variable (maybe, named differently). Something like:

 Public myRibbon As IRibbonUI

The code line which I added (myRibbon.InvalidateControl "ComboBox001") is only meant to trigger ComboBox001_getText...

If something not clear enough, do not hesitate to ask for clarifications.

Edited:

My missed solution for updating the combo when manually changing the sheets looks like that:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim arrSh, cbVal As String
  arrSh = Split("One,Two,Three", ",")
  If Sh.Index <= 3 Then
     cbVal = arrSh(Sh.Index - 1)
     SaveSetting myApp, mySett, myVal, cbVal
     myRibbon.InvalidateControl "ComboBox001"
  End If
End Sub