VBA - Custom tab Excel menu - Combobox - reset after user choice

Question

Good morning, I created a personalized menu with Office Ribbon X editor, including a combo box with two 3 options (Choose, ascending and descending). when the user chooses descending, a routine, sorting the column is executed in ascending order, and for the other option sorting descending. once the sorting routine is completed, I would like the combo box to return to the "choose"(Choix in French) item which does not perform any action. I tried to find a solution but I remain DRY. code XML

    <comboBox id="Tri_Col" label="Tri_colonne" 
    screentip="Tri la base selon le contenu de la colonne Active"
    supertip="Filtrez la base des écriture selon la colonne active, vous pourrez      retrouver un ordre logique avec le bouton tri complet" 
    onChange="Tri_Col" 
    getItemLabel="Tri_ColGetLabel"
    getItemCount="Tri_ColGetCount" >
    <!--Chaque item correspond à un élément de la liste déroulante :-->
    <item id="itF0" label="Choix" imageMso="Help" />  
     <item id="itF1" label="Ascendant" imageMso="SortAscendingExcel" /> 
     <item id="itF2" label="Descendant" imageMso="SortDescendingExcel" /> 
     </comboBox>

Code VBA

    '*Callback for Tri_Col onChange
    Sub Tri_Col(control As IRibbonControl, text As String)
    'Suivre le choix de l'utilisateur
    MsgBox "Vous avez choisi : " & text
    'Exécuter le code TrierTableau_Col
    TrierTableau_Col text

    '>>>>here code to restore Tri_Col  cotrol on "Choix" ?
    End Sub

Thanks to anyone who can help me find a solution.

Philippe

Answer 1

I would like the combo box to return to the "choose"(Choix in French) item which does not perform any action.

When you need to update the state of your ribbon controls use callbacks where you could return the required state for control(s) and call the Invalidate or InvalidateControl methods of the IRibbonUI interface to get the callbacks invoked by the Office and update the ribbon controls state.

For example, if an add-in writer implements the getEnabledcallback procedure for a button, the function is called once, the state loads, and then if the state needs to be updated, the cached state is used instead of recalling the procedure. This process remains in place for the control until the add-in signals that the cached values are invalid by using the InvalidateControl method, at which time, the callback procedure is again called and the return response is cached.

To get an instance of the IRibbonUI interface you need to specify the onLoad callback:

<customUI … OnLoad="MyAddInInitialize" …>

Then in the code you could use the following:

Dim MyRibbon As IRibbonUI 
 
Sub MyAddInInitialize(Ribbon As IRibbonUI) 
 Set MyRibbon = Ribbon 
End Sub 
 
Sub myFunction() 
 MyRibbon.InvalidateControl("control1") ' Invalidates the cache of a single control 
End Sub

Answer 2

''' Public MyRibbon As IRibbonUI

 Sub RibbonLoaded(ribbon As IRibbonUI)
     Set MyRibbon = ribbon
 End Sub

 Sub myFunction()
  MyRibbon.InvalidateControl ("Tri_Col") ' Invalidates the cache of a single control
 End Sub

 '*Callback for Tri_Col onChange
 Sub Tri_Col(control As IRibbonControl, text As String)
      'Suivre le choix de l'utilisateur
  MsgBox "Vous avez choisi : " & text
  'Exécuter le code TrierTableau_Col
   TrierTableau_Col text
 'here code to restore Tri_Col  control on "Choix" ?

 End Sub

 <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonLoaded">
    <!-- Modife les commandes standard Excel Doit etre placé avant STARTFROMSCRTCH-->   
    <commands>
 etc......




  <comboBox id="Tri_Col" label="Tri_colonne" 
screentip="Tri la base selon le contenu de la colonne Active"
    supertip="Filtrez la base des écriture selon la colonne active, vous pourrez retrouver un ordre logique avec le bouton tri complet" 
onChange="Tri_Col" 
getItemLabel="Tri_ColGetLabel"
getItemCount="Tri_ColGetCount" 
    <item id="itF0" label="Choix" imageMso="Help" />  
    <item id="itF1" label="Ascendant" imageMso="SortAscendingExcel" /> 
    <item id="itF2" label="Descendant" imageMso="SortDescendingExcel" /> 
    </comboBox>>

 ETC....'''hello and thank you, I don't really understand

<customUI … OnLoad="MyAddInInitialize" …>

However, everything else seems ok.

what I would like is that following the selection of an option of the "Tri_Col" control, and execution of the callback code, the displayed option returns to the "Choice" option. testing the procedure.. Sub myFunction() MyRibbon.InvalidateControl("control1") ' Invalidates the cache of a single control End Sub generates error 91