VBA CustomUI activate or deactivate control

activating and deactivating a button on the excel ribbon

This code VBA return error in "Actualise_CTRL process".

objRuban.InvalidateControl "Bouton1"

Erro 91 variable or block object with undefined

the html code is lower

I've been stuck on this topic since this afternoon. In principle this set of code should enable or disable control of the custom tab of the EXCEL ribbon, but there must be something I'm missing.

Thanks a lot for any help on the subject


Option Explicit

Public objRuban As IRibbonUI
 boolResult As Boolean


'Callback for customUI.onLoad
'Est déclenché lors du chargement du ruban personnalisé.
Sub RubanCharge(ribbon As IRibbonUI)
   boolResult = False
   Set objRuban = ribbon
End Sub


'Callback for Bouton1 onAction
'La procédure déclenchée lorsque vous cliquez sur le bouton.
Sub ProcLancement(control As IRibbonControl)
   MsgBox "ma procédure."
End Sub


'Callback for Bouton1 getEnabled
'Active ou désactive le bouton en fonction de la variable boolResult
Sub Bouton1_Enabled(control As IRibbonControl, ByRef returnedVal)
    returnedVal = boolResult
End Sub


Sub Actualise_CTRL(ByVal Target As Range)

'Vérifie si la cellule A1 est modifiée et si la cellule contient la valeur 1.
    If Target.Address = "$A$1" And Target = 1 Then
       boolResult = True
   Else
       boolResult = False
    End If
        
'Rafraichit le bouton personnalisé

    
   objRuban.InvalidateControl "Bouton1"

End Sub

The ribbon XML:

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


    
<!-- Indiquez True pour masquer tous les autres onglets standards-->
<ribbon startFromScratch="false">
    
    <tabs>

<tab id="OngletPerso" label="OngletPerso" visible="true">
   <group id="Projet01" label="Projet 01">

      <!-- onAction="ProcLancement" définit la macro déclenchée lorsque vos cliquez sur le bouton. -->
      <!-- getEnabled="Bouton1_Enabled" gére la condition d'activation ou de désactivation. -->       
     <button id="Bouton1" label="Lancement" onAction="ProcLancement" size="normal" 
         imageMso="Repeat" getEnabled="Bouton1_Enabled"/> 

   </group>
</tab>

</tabs>

</ribbon>
</customUI>

Solution

To initialize the objRuban instance in the code you need to handle the onLoad callback of your ribbon XML.

So, in your ribbon XML markup you need to add the following attribute:

<customUI … onLoad="RubanCharge" …>

And then in the code you may rely on the objRuban instance initialized correctly.

You may find the Dynamically updating the ribbon section helpful.