[SOLVED] Excel Ribbon: Use one custom ribbon button to enable all other custom ribbon buttons

Excel Ribbon: Use one custom ribbon button to enable all other custom ribbon buttons

This is based on Ron de Bruin's code "Enable or Disable custom ribbon buttons".

The following example ribbon consists of two tabs:

'Tools': empty for now
'Macros': 3 groups

All buttons are built with tag and getEnabled. So by default 'Group6' and 'Group7' are disabled, 'Group8' with 'Button57' is enabled. I’d like to enable 'Group6' and 'Group7' with 'Button57' (password protected).

Running the macro EnableAllControls by pressing play in the VBA editor works as expected.

But using Call does not work:

'Callback for Button57 onAction
Sub c001_01_EnableTabMacros(control As IRibbonControl)
    Call EnableAllControls
End Sub

How do you Call EnableAllControls?

' -- XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" 
 onLoad="LoadRibbon">   
  <ribbon>  
    <tabs>  
      <tab id="ToolsV1.0.0" label="Tools" insertAfterMso="Developer">   
        <!-- built-in controls + a couple of macro buttons -->
      </tab>    
      <tab id="MacrosV4.0.0" label="Macros" insertAfterMso="ToolsV1.0.0">
        <group id="Group6" 
               label="Chart Editing">         
          <button id="Button3_1" label="Del Textbox" 
                                 size="normal" 
                                 onAction="a003_01_DeleteTextBoxesFromChart_v1_0" 
                                 imageMso="ClearFormatting"
                                 tag="Group6Button3_1"
                                 getEnabled="GetEnabledMacro"/> 
          <button id="Button3_2" label="Del Zeroes" 
                                 size="normal" 
                                 onAction="a003_02_DeleteLabelsValueZero_v1_0" 
                                 imageMso="ChartDataLabel"
                                 tag="Group6Button3_2"
                                 getEnabled="GetEnabledMacro"/>              
        </group>  
        <group id="Group7" 
               label="Protect">   
          <button id="Button17" label="ChtLock" 
                                size="large" 
                                onAction="a003_03_ChartProtectFormatting_v1_0"
                                imageMso="Lock"                                 
                                tag="Group7Button17"
                                getEnabled="GetEnabledMacro"/> 
        </group>         
        <group id="Group8" 
               label="Code">   
          <button id="Button57" label="Macros 4.0" 
                                size="large" 
                                onAction="c001_01_EnableTabMacros"
                                imageMso="MicrosoftVisualFoxPro"
                                tag="Group8Button57"
                                getEnabled="GetEnabledMacro"/>                               
        </group>            
      </tab>               
    </tabs> 
  </ribbon> 
</customUI> 



' -- Standard Module "RibbonModule"

Option Explicit   
Public RibUI As IRibbonUI  
Public MyTag As String 

Sub LoadRibbon(ribbon As IRibbonUI)
Set RibUI = ribbon
    Call EnableControlsWithCertainTag8
End Sub

Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
    If MyTag = "Enable" Then
        Enabled = True
    Else
        If control.Tag Like MyTag Then
            Enabled = True
        Else
            Enabled = False
        End If
    End If
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If RibUI Is Nothing Then
        MsgBox "Error, Save/Restart your workbook" & vbNewLine & _
        "Visit this page for a solution for Win Excel :  _
        http://www.rondebruin.nl/ribbonstate.htm"
    Else
        RibUI.Invalidate
    End If
End Sub

'Note: Do not change the code above

Sub EnableControlsWithCertainTag8()
'Enable only the controls with a Tag that start with "Group8"
    Call RefreshRibbon(Tag:="Group8*")
End Sub

Sub EnableAllControls() '<- works: press play in vbe
'Enable all controls 
Dim MyPW As String
    MyPW = "pw"
    If InputBox("Enter password to continue.",  _
            "Enter Password") <> MyPassword Then
        Exit Sub
        End If
    Call RefreshRibbon(Tag:="*")
End Sub



' -- Standard Module "SubModule"

Option Explicit

' =========
' Callbacks
' =========

'Callback for Button57 onAction
Sub c001_01_EnableTabMacros(control As IRibbonControl)
    Call EnableAllControls
End Sub

'Callback for Button3_1 onAction
Sub a003_01_DeleteTextBoxesFromChart_v1_0(control As IRibbonControl)
    '...
End Sub

'Callback for Button3_2 onAction
Sub a003_02_DeleteLabelsValueZero_v1_0(control As IRibbonControl)
    '... 
End Sub

'Callback for Button3_3 onAction
Sub a003_03_ChartProtectFormatting_v1_0(control As IRibbonControl)
    '...  
End Sub



' -- ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    MyTag = "Enable"
End Sub

Solution

Through some trial and elimination, it appears that because the callback sub's name starts with the letter c as in c001_01_EnableTabMacros the compiler is not able to see that name. Changing the leading c to another, say d, it does work.