excel - 如何在工作表上设置保护但启用大纲和过滤器，重新启动后可用？

Question

我想向客户发送价目表并且需要保护工作表(只有两个)。但是，我想允许分组/概述和过滤数据。由于工作表应该发送给客户，我不想打扰他任何事情。

我尝试使用所需条件设置密码，但无论何时我重新打开工作簿，分组/大纲都会被锁定。

我能想到的是尝试数据透视表？

Option Explicit
Sub protection()

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet1")

Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("Sheet2")

 Dim strPassword As String: strPassword = "***"


    With ws1

        .Protect Password:=strPassword, UserInterfaceOnly:=True, AllowFiltering:=True
        .EnableOutlining = True

    End With


    With ws2

        .Protect Password:=strPassword, UserInterfaceOnly:=True, AllowFiltering:=True

        .EnableOutlining = True

    End With


End Sub

Answer 1

重新打开工作簿后，您需要重置 UserInterfaceOnly 的工作表保护才能工作。

Private Sub Workbook_Open()
    protection
End Sub    

Sub protection()
    Const Password = "***"        
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2"))
        With ws  
            .Protect Password:=Password , UserInterfaceOnly:=True, AllowFiltering:=True
            .EnableOutlining = True
        End With
    Next    
End Sub