events - 从下拉列表中选择时触发事件

Question

我需要当用户从下拉菜单中选择一个选项时，它将触发事件并锁定某些单元格范围。我获得了锁定单元格的代码，但当我选择下拉菜单时无法将其锁定。下拉菜单中字符串数据的值为ZFB50

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$K$2" Then

    With Application
     .EnableEvents = False
     .ScreenUpdating = False
     .Calculation = xlCalculationManual
    End With

If Target.Address = "ZFB50" Then

    ActiveSheet.Unprotect

    Range("E8:E100").Select
    Selection.Locked = True

    Range("C8:C100").Select
    Selection.Locked = True

    Range("D8:D100").Select
    Selection.Locked = True

    Range("F8:F100").Select
    Selection.Locked = True

    Next cell

    ActiveSheet.Protect

    With Application
     .EnableEvents = True
     .ScreenUpdating = True
     .Calculation = xlCalculationAutomatic
    End With

End If

End Sub

还是不行，这段代码有什么问题吗？

Answer 1

如果您使用数据验证下拉列表，则可以使用 Worksheet_Change 事件，如下所示:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    ' Code to lock ranges goes here

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End If
End Sub

这假设您的数据验证位于单元格 A1 中。您必须根据您的情况更新引用。