excelvbadisableribbonx

Excel Ribbon: Disable all custom tabs if worksheet is protected

If a worksheet is protected, most of the built-in buttons are greyed out.

How do you do this with a custom ribbon?

How do you say: If the worksheet is protected, disable all custom tabs / all buttons in custom tabs and turn them back on, if the sheet is unprotected?

Framework XML, Standard Module, ThisWorkbook:

' -- XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
 onLoad="LoadRibbon">
  <ribbon>
    <tabs>
      <tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">                  
       ' groups/buttons 
      </tab>
    </tabs>
  </ribbon>
</customUI>


' -- Standard Module

Option Explicit
Public RibUI As IRibbonUI

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

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


' -- ThisWorkbook

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

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    RibUI.InvalidateControl "xy"
End Sub

Framework Ribbon with 2 Tabs, "Tools" and "Macros":

<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">
       ' macro buttons      
      </tab>               
    </tabs> 
  </ribbon> 
</customUI>
Solution

  • Basically, it is needed to add getEnabled on controls you want to conditionally Enable - Disable (in XML), then Invalidate the respective controls. I will try showing a small such example:

    1. The suggested solution refers the next XML, inserting two Tabs, each of them containing a control. After Edit I added <commands> for standard "SheetProtect" idMso, hooking its OnAction event:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonLoaded_Addin" xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
  <commands>
      <command idMso="SheetProtect" onAction="mySheetProtect" />
  </commands>
  <ribbon> 
    <tabs> 
      <tab id="ToolsV1.0.0" label="TOOLS"  insertAfterMso="Developer"> 
        <group id="GroupDemo" label="Test group1"> 
           <dropDown id="TestDrD"  
                    label = "Test dropDown:"
                    getEnabled="Test_getEnabled"/>
                     
        </group>        
    </tab>
    <tab id="MacrosV4.0.0" label="Macros" insertAfterMso="ToolsV1.0.0">  
        <group id="GroupDemo2" label="Test group2"        
            imageMso="AddInManager">    
             <checkBox id="myCheckbox" 
                     getEnabled="Chk_getEnabled" /> 
        </group>       
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>
    1. In a standard module you need to add the next declarations (on top of it, in the declarations area) and API functions. The API functions (not required) are a bonus, to refresh MyRibbon object when it is lost (in case of VBA errors):
    Option Explicit

'To memorize the Ribbon object! ___________
#If VBA7 Then
        Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal length As LongPtr)
#Else
        Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If
'_________________________________________


Public myRibbon As IRibbonUI
Const strRib As String = "\MyRibbX" 'where to save the pointer...

Public boolEnableChk As Boolean, boolDrD As Boolean 'to Disable/Enable

    Copy also the next procedures in that standard module: a. To run on Ribbon is loaded (memorize the Ribbon pointer and initially set as Enabled the necessary controls):

    Sub RibbonLoaded_Addin(ribbon As IRibbonUI)
    Dim Path As String: Path = Environ("temp") & strRib
    Dim File As Integer: File = FreeFile
    Open Path For Output As #File
        Print #File, ObjPtr(ribbon) 'memorize IRibbonUI pointer
    Close #File

   Set myRibbon = ribbon
   
   boolEnableChk = True: boolDrD = True 'to initialy enable controls
   myRibbon.InvalidateControl "myCheckbox" 'to update it
   myRibbon.InvalidateControl "TestDrD"
End Sub

    b. The sub able to recuperate the lost Ribbon object:

    Sub getRibbon() 'reSet myRibbon if it was lost (it's Nothing):
    Dim Path As String: Path = Environ("temp") & strRib
    Dim File As Integer: File = FreeFile
    Dim ribValue As String
    
    If myRibbon Is Nothing Then
        Open Path For Input As #File
            Input #File, ribValue
        Close #File
        
        #If VBA7 Then
            CopyMemory myRibbon, CLngPtr(ribValue), 8 'place in memory IRibbonUI object from its memorized pointer '64 bit
        #Else
            CopyMemory myRibbon, CLng(ribValue), 4    'place in memory IRibbonUI object from its memorized pointer
        #End If
    End If
End Sub

    c. The two necessary getEnabled events:

    Sub Chk_getEnabled(control As IRibbonControl, ByRef enabled)
  enabled = boolEnableChk
End Sub

Sub Test_getEnabled(control As IRibbonControl, ByRef enabled)
  enabled = boolDrD
End Sub

    d. Two (only) testing subs to see how the Ribbon controls are alternatively changed when play with them?

    Sub testMakeEn_Dis_ChkBox() 'pressing alternatively it will enable/disable the check box
  boolEnableChk = Not boolEnableChk
  
  If myRibbon Is Nothing Then getRibbon
  myRibbon.InvalidateControl ("myCheckbox")
End Sub

Sub testMakeEn_Dis_DropD() 'pressing alternatively it will enable/disable the DropDown
  boolDrD = Not boolDrD
  
  If myRibbon Is Nothing Then getRibbon
  myRibbon.InvalidateControl ("TestDrD")
End Sub

    e. And the next Sub able to replace the above testing ones, having a parameters to define the control name to be Enabled\Disabled:

    Sub Invalidate_Control(controlName As String) 'to be used externaly
  If myRibbon Is Nothing Then getRibbon
  myRibbon.InvalidateControl controlName
End Sub
    1. Please, copy the next code in ThisWorkbook code module. Workbook_SheetActivate event is triggered when you activate different sheets and according to its evaluation related to protections Enables or Disables the necessary controls:
    Option Explicit

Private Sub Workbook_SheetActivate(ByVal sh As Object)
  If sh.ProtectContents Then
    boolEnableChk = False
    'boolDrD = False 'uncomment to also use it
  Else
    boolEnableChk = True
    'boolDrD = True 'uncomment to also use it
  End If
  Invalidate_Control "myCheckbox"
  'Invalidate_Control "TestDrD" 'uncomment to also use it
End Sub

    Please, send some feedback after testing it. In a following comment I will also send a tested workbook (using a transfer site, it can be downloaded in a limited period of time)...

    Edit: To trigger the "Protect Sheet" button click you should add it in the XML part (between <commands> - </commands>), see above and in VBA you need to also copy the next Sub, able to trigger/hook button 'OnAction` event and use it:

    Sub mySheetProtect(ByVal control As IRibbonControl, ByRef cancelDefault)
  Dim ShPr As CommandBarButton
  Set ShPr = Application.CommandBars.FindControl(msoControlButton, ID:=893) 'legacy way of finding the control caption...
                                                                            'control getText is not exposed...
  If ShPr.Caption = "&Protect Sheet..." Then
    boolEnableChk = False
    boolDrD = False
  Else
    boolEnableChk = True
    boolDrD = True
  End If
  Invalidate_Control "TestDrD": Invalidate_Control "myCheckbox"

  cancelDefault = False 'without it protect/unprotect is cancelled...
End Sub

    Please, send some feedback after testing it.