Лента Excel: отключить все настраиваемые вкладки, если лист защищен

По сути, необходимо добавить getEnabled к элементам управления, которые вы хотите условно включить или отключить (в XML), а затем Invalidate соответствующие элементы управления. Я попробую показать небольшой такой пример:

1. Предлагаемое решение ссылается на следующий XML-код, вставляя две вкладки, каждая из которых содержит элемент управления. После редактирования я добавил <commands> для стандартного "SheetProtect"idMso, перехватив его событие OnAction:

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

2. В стандартный модуль необходимо добавить следующие объявления (поверх него, в области объявлений) и функции API. Функции API (не обязательные) являются бонусом для обновления объекта MyRibbon в случае его потери (в случае ошибок VBA):

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

Скопируйте также следующие процедуры в этот стандартный модуль: а. Для запуска на загруженной ленте (запомните указатель ленты и изначально установите необходимые элементы управления как «Включено»):

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

б. Субмарина способна восстановить потерянный объект «Лента»:

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

в. Два необходимых getEnabled события:

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

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

д. Два (единственных) тестовых сабвуфера, чтобы увидеть, как элементы управления на ленте поочередно изменяются при игре с ними?

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

е. И следующий Sub, способный заменить вышеупомянутые тестовые, имеющий параметры для определения имени элемента управления: Enabled\Disabled:

Sub Invalidate_Control(controlName As String) 'to be used externaly
  If myRibbon Is Nothing Then getRibbon
  myRibbon.InvalidateControl controlName
End Sub

3. Пожалуйста, скопируйте следующий код в модуль кода ThisWorkbook. Workbook_SheetActivate событие запускается, когда вы активируете разные листы и в соответствии с его оценкой, связанной с защитой, включает или отключает необходимые элементы управления:

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

Пожалуйста, оставьте отзыв после тестирования. В следующем комментарии я также пришлю проверенную рабочую тетрадь (с помощью трансферного сайта ее можно будет скачать в течение ограниченного периода времени)...

Редактировать: Чтобы вызвать нажатие кнопки "Protect Sheet", вы должны добавить его в часть XML (между <commands> - </commands>), см. выше, а в VBA вам также необходимо скопировать следующий Sub, способный запускать/перехватывать событие кнопки «OnAction» и использовать его:

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

Пожалуйста, оставьте отзыв после тестирования.