基于其他单元格值锁定/解锁单元格的VBA代码失效问题

需求：根据Permission_Cell的值锁定或解锁Data_Cell：

• 当Permission_Cell值为“No”时，Data_Cell设置公式=Some_Other_Named_Range并锁定；
• 值为其他时，Data_Cell解锁可编辑。

编写的VBA代码如下：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  With ThisWorkbook.Worksheets("Sheet1")
  
    Application.EnableEvents = False
  
    .Unprotect Password:=""
    
    If Not Intersect(Target, .Range("Permission_Cell")) Is Nothing Then
      If .Range("Permission_Cell").Value = "No" Then
        .Range("Data_Cell").Formula = "=Some_Other_Named_Range"
        .Range("Data_Cell").Locked = True
      Else
        .Range("Data_Cell").Locked = False
      End If
    End If
    
  Application.EnableEvents = True
  .Protect Password:="", AllowFiltering:=True, AllowSorting:=True, UserInterFaceOnly:=True, AllowFormattingCells:=True, DrawingObjects:=True
    
  End With
  
End Sub

问题现象：将Permission_Cell设为“No”后，Excel显示工作表受保护且Data_Cell锁形图标高亮，但仍可编辑该单元格；手动取消保护再重新保护后，Data_Cell才会被正确锁定。

核心问题出在**UserInterFaceOnly:=True**参数上：

• 该参数的作用是让工作表仅限制用户手动操作，但允许VBA自由修改受保护单元格。
• 在这种模式下，通过VBA修改单元格的Locked属性后，新的锁定状态不会自动同步到用户界面的保护规则中——Excel不会实时刷新用户可编辑的单元格范围，必须重新触发标准的保护流程才能生效。
• 另外，设置Data_Cell公式时的临时编辑状态，也会导致后续设置的Locked=True无法立即在用户界面层面生效。

有两种可行的修正方式：

方式一：移除UserInterFaceOnly参数（推荐） #

改用标准工作表保护模式，这样重新保护时会正确应用Locked属性的设置：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  With ThisWorkbook.Worksheets("Sheet1")
  
    Application.EnableEvents = False
  
    .Unprotect Password:=""
    
    If Not Intersect(Target, .Range("Permission_Cell")) Is Nothing Then
      If .Range("Permission_Cell").Value = "No" Then
        .Range("Data_Cell").Formula = "=Some_Other_Named_Range"
        .Range("Data_Cell").Locked = True
      Else
        .Range("Data_Cell").Locked = False
      End If
    End If
    
    Application.EnableEvents = True
    ' 使用标准保护模式，移除UserInterFaceOnly参数
    .Protect Password:="", AllowFiltering:=True, AllowSorting:=True, AllowFormattingCells:=True, DrawingObjects:=True
    
  End With
  
End Sub

方式二：强制刷新保护状态 #

如果需要保留UserInterFaceOnly模式，可以先以标准模式保护再切换回来，强制刷新锁定状态：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  With ThisWorkbook.Worksheets("Sheet1")
  
    Application.EnableEvents = False
  
    .Unprotect Password:=""
    
    If Not Intersect(Target, .Range("Permission_Cell")) Is Nothing Then
      If .Range("Permission_Cell").Value = "No" Then
        .Range("Data_Cell").Formula = "=Some_Other_Named_Range"
        .Range("Data_Cell").Locked = True
      Else
        .Range("Data_Cell").Locked = False
      End If
    End If
    
    Application.EnableEvents = True
    ' 先以标准模式保护，再切换回UserInterFaceOnly，强制刷新锁定规则
    .Protect Password:="", AllowFiltering:=True, AllowSorting:=True, AllowFormattingCells:=True, DrawingObjects:=True
    .Unprotect Password:=""
    .Protect Password:="", AllowFiltering:=True, AllowSorting:=True, UserInterFaceOnly:=True, AllowFormattingCells:=True, DrawingObjects:=True
    
  End With
  
End Sub

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