如何通过VBA修改AllowEditRanges中Test条目对应的可编辑范围

问题根因 #

只有工作表处于未保护状态时，才允许修改AllowEditRanges条目的关联范围。工作表处于保护状态时直接修改Range属性不会抛出异常，属于静默失败，是Excel VBA的既定行为。

修正后的代码示例 #

Option Explicit
Option Compare Text

Private Sub Modify_User_EditRange()
    Dim ws As Worksheet
    Dim protectPwd As String
    ' 如果工作表保护有密码请在这里填写，无密码则留空
    protectPwd = ""
    
    Set ws = ThisWorkbook.ActiveSheet
    
    ' 先判断工作表是否处于保护状态，是则先取消保护
    If ws.ProtectionMode Then
        ws.Unprotect Password:=protectPwd
    End If
    
    ' 修改允许编辑范围
    ws.Protection.AllowEditRanges("Test").Range = ws.Range("A2:A8")
    
    ' 重新开启工作表保护，可根据需要调整Protect方法的参数
    ws.Protect Password:=protectPwd, _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=False, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

注意事项 #

• 如果你的工作表保护设置了密码，需要给Unprotect和Protect方法的Password参数传入正确的密码
• 可以根据实际业务需求调整ws.Protect的参数，保留你需要的工作表操作权限
• 如果需要完全复用原有保护配置，可以在取消保护前先把ws.Protection的各个属性值暂存，重新保护时原样传入即可

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