使用VBA复制Excel工作表丢失保护，寻求更安全的保护方案

Let me walk you through practical, secure fixes tailored to your exact needs—this is the kind of problem I troubleshoot all the time:

1. Fixing Lost Protection When Copying Templates #

When you copy a protected worksheet, the UserInterfaceOnly protection flag doesn’t transfer to the new sheet—that’s the root cause of your lost protection. Here’s how to copy the sheet and retain all the security rules you need:

Step-by-Step Fix: #

• First, grab all the protection settings (like allowed user actions) from your template sheet.
• Copy the template as normal.
• Reapply those saved settings to the new sheet, making sure to use UserInterfaceOnly:=True—this lets your VBA interact with the sheet (for grouping, etc.) while locking down the user interface to hide formulas.

Example Copy & Protection Code: #

Sub CopyProtectedTemplateSheet()
    Dim templateSheet As Worksheet
    Dim newSheet As Worksheet
    ' Capture all allowed user actions from the template
    Dim allowFormatCells As Boolean, allowFormatColumns As Boolean
    Dim allowFormatRows As Boolean, allowInsertColumns As Boolean
    Dim allowInsertRows As Boolean, allowInsertHyperlinks As Boolean
    Dim allowDeleteColumns As Boolean, allowDeleteRows As Boolean
    Dim allowSort As Boolean, allowAutoFilter As Boolean, allowPivotTables As Boolean
    
    ' Set your template sheet (adjust name to match yours)
    Set templateSheet = ThisWorkbook.Sheets("Template1")
    
    ' Pull existing allowed actions from the template's protection
    With templateSheet.Protection
        allowFormatCells = .AllowFormatCells
        allowFormatColumns = .AllowFormatColumns
        allowFormatRows = .AllowFormatRows
        allowInsertColumns = .AllowInsertColumns
        allowInsertRows = .AllowInsertRows
        allowInsertHyperlinks = .AllowInsertHyperlinks
        allowDeleteColumns = .AllowDeleteColumns
        allowDeleteRows = .AllowDeleteRows
        allowSort = .AllowSort
        allowAutoFilter = .AllowAutoFilter
        allowPivotTables = .AllowPivotTables
    End With
    
    ' Copy the template to a new sheet at the end of the workbook
    templateSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set newSheet = ActiveSheet
    
    ' Reapply protection to the new sheet with your required restrictions
    newSheet.Protect _
        Password:=GetSecureProtectionPassword(), ' Secure password (no hardcoding!)
        UserInterfaceOnly:=True,
        AllowFormatCells:=allowFormatCells,
        AllowFormatColumns:=allowFormatColumns,
        AllowFormatRows:=allowFormatRows,
        AllowInsertColumns:=allowInsertColumns,
        AllowInsertRows:=allowInsertRows,
        AllowInsertHyperlinks:=allowInsertHyperlinks,
        AllowDeleteColumns:=allowDeleteColumns,
        AllowDeleteRows:=allowDeleteRows,
        AllowSort:=allowSort,
        AllowAutoFilter:=allowAutoFilter,
        AllowPivotTables:=allowPivotTables,
        AllowEditObjects:=False, ' Block editing objects per your request
        AllowEditScenarios:=False ' Block editing scenarios per your request
End Sub

2. Secure Protection Without Hardcoding Passwords #

Hardcoding passwords in VBA is a huge security risk—anyone can view your code and steal the password. Instead, store your password in Custom Document Properties (it’s hidden from regular users and only accessible via VBA):

Step 1: Store the Password #

1. Open your workbook, go to File > Info > Properties > Advanced Properties.
2. Switch to the Custom tab, add a new property:
   • Name: SheetProtectionPass
   • Type: Text
   • Value: Your chosen password
3. Click Add then OK.

Step 2: Retrieve the Password in VBA #

Add this helper function to your module to fetch the password without exposing it in your code:

Function GetSecureProtectionPassword() As String
    Dim customProp As DocumentProperty
    
    ' Try to grab the stored password
    On Error Resume Next
    Set customProp = ThisWorkbook.CustomDocumentProperties("SheetProtectionPass")
    On Error GoTo 0
    
    If Not customProp Is Nothing Then
        GetSecureProtectionPassword = customProp.Value
    Else
        ' Fallback if the property is missing (adjust this as needed)
        GetSecureProtectionPassword = InputBox("Enter sheet protection password:", "Password Required")
    End If
End Function

3. Enabling Grouping via Workbook_Open Event #

The UserInterfaceOnly setting resets every time you close the workbook, so you need to reapply it on open to keep grouping working. Here’s how to set this up:

1. Open the VBA Editor (Alt + F11), double-click ThisWorkbook in the Project Explorer.
2. Paste this code into the code window:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Sheets
        ' Target your template sheets and their copies (adjust the name filter as needed)
        If ws.Name Like "Template*" Or ws.Name Like "Copy of Template*" Then
            ' Reapply protection with UserInterfaceOnly enabled
            ws.Protect _
                Password:=GetSecureProtectionPassword(),
                UserInterfaceOnly:=True,
                AllowFormatCells:=True, ' Match your allowed actions here
                AllowFormatColumns:=True,
                AllowFormatRows:=True,
                AllowInsertColumns:=True,
                AllowInsertRows:=True,
                AllowInsertHyperlinks:=True,
                AllowDeleteColumns:=True,
                AllowDeleteRows:=True,
                AllowSort:=True,
                AllowAutoFilter:=True,
                AllowPivotTables:=True,
                AllowEditObjects:=False,
                AllowEditScenarios:=False
            
            ' Enable default grouping (adjust levels to match your needs)
            ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        End If
    Next ws
End Sub

Quick Tips: #

• UserInterfaceOnly:=True is non-negotiable here—it lets your VBA handle grouping while keeping users from editing hidden formulas.
• Tweak the allowed actions in the Protect method to match your exact needs (you already specified blocking edit objects and scenarios, which are set to False above).
• Grouping will work smoothly because your VBA has full access to the sheet, even though the user can’t make unauthorized changes.

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