excel - Trigger event when select from dropdown

Question

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?

Answer 1

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

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