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

问题描述：

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

I need when a user selects an option from the dropdown menu, it will trigger the event and lock down certain range of cells. I got the codes for lockdown cell but I can't lock it down when I select the dropdown menu. The value of the string data in the dropdown menu is 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

它仍然不起作用，这个代码是否有问题？

It still doesn't work, is there any problem with this code?

答

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

If you're using a data validation dropdown, you can use the Worksheet_Change event like so:

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。您必须根据您的情况更新参考。

This assumes that your data validation is in cell A1. You'll have to update the reference as appropriate for your situation.

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

相关推荐