VBA重置密码重保护后Format列/行、自动筛选选项失效求助

使用VBA重置Excel文件密码并重新保护工作表后，「格式列」「格式行」「自动筛选」选项会被禁用。我们会根据需求为部分工作表手动启用这些选项，但每次通过宏重置密码重保护时，这些选项都会被关闭。需要修改代码，确保重保护后保留工作表之前已启用的这些权限。

原代码如下：

Sub Main_Protect()
    
    Dim PWD As String
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim sht As Worksheet
    
    PWD = ThisWorkbook.Sheets("Main").Range("D1").Value
    
    'If PWD = "" Then
        'PWD = InputBox("Please Enter the password")
    'End If
        
    Set WB = Application.Workbooks.Open(ThisWorkbook.Sheets("Main").Cells(13, 1))
    
          ' Loop through all sheets in the workbook
    
    LR = ThisWorkbook.Sheets("Tab").Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    
    Set rng = ThisWorkbook.Sheets("Tab").Range("A2:A" & LR)
    
    For Each cell In rng
    If cell > 0 Then
    
         WB.Activate
        
        Set ws = WB.Sheets(cell.Value)
        ws.Protect PWD,Contents:=True, UserInterfaceOnly:=True
        
      ThisWorkbook.Sheets("Tab").Range("B" & ThisWorkbook.Sheets("Tab").Cells(Rows.Count, 2).End(xlUp).Row + 1).Value = "Yes"
      
    
        End If
    Next cell
    
    MsgBox "Done"
    WB.Save
    WB.Close
    
End Sub

要保留工作表原有的格式列、格式行、自动筛选权限，需要在重保护前先读取每个工作表当前的允许设置，再在Protect方法中传入对应的参数。修改后的代码如下：

Sub Main_Protect()
    Dim PWD As String
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim allowFormatColumns As Boolean
    Dim allowFormatRows As Boolean
    Dim allowAutoFilter As Boolean
    
    PWD = ThisWorkbook.Sheets("Main").Range("D1").Value
    
    Set WB = Application.Workbooks.Open(ThisWorkbook.Sheets("Main").Cells(13, 1))
    
    ' 修复原代码中ActiveSheet的潜在问题，明确指定Tab工作表
    LR = ThisWorkbook.Sheets("Tab").Cells(ThisWorkbook.Sheets("Tab").Rows.Count, "A").End(xlUp).Row
    Set rng = ThisWorkbook.Sheets("Tab").Range("A2:A" & LR)
    
    For Each cell In rng
        If cell.Value > 0 Then
            Set ws = WB.Sheets(cell.Value)
            
            ' 读取当前工作表已启用的权限状态
            allowFormatColumns = ws.Protection.AllowFormattingColumns
            allowFormatRows = ws.Protection.AllowFormattingRows
            allowAutoFilter = ws.Protection.AllowAutoFilter
            
            ' 重保护时传入保留的权限参数
            ws.Protect _
                Password:=PWD, _
                Contents:=True, _
                UserInterfaceOnly:=True, _
                AllowFormattingColumns:=allowFormatColumns, _
                AllowFormattingRows:=allowFormatRows, _
                AllowAutoFilter:=allowAutoFilter
                
            ThisWorkbook.Sheets("Tab").Range("B" & ThisWorkbook.Sheets("Tab").Cells(ThisWorkbook.Sheets("Tab").Rows.Count, 2).End(xlUp).Row + 1).Value = "Yes"
        End If
    Next cell
    
    MsgBox "Done"
    WB.Save
    WB.Close
End Sub

• 原代码的ws.Protect方法仅指定了Contents和UserInterfaceOnly参数，其余权限参数默认设为False，会覆盖之前手动开启的设置。
• 通过ws.Protection对象的以下属性，可以读取工作表当前的权限状态：
  • AllowFormattingColumns：记录是否允许格式列操作
  • AllowFormattingRows：记录是否允许格式行操作
  • AllowAutoFilter：记录是否允许自动筛选操作
• 在调用Protect方法时，将这些读取到的状态作为参数传入，就能保留原有启用的权限，不会被宏重置。
• 额外修复了原代码中ActiveSheet.Rows.Count的潜在问题，明确指定为Tab工作表的行计数，避免因当前激活工作表变化导致的错误。

内容的提问来源于stack exchange，提问作者Ramu