Trigger event when selecting from the drop-down list

Question

I need it, when the user selects a parameter from the drop-down menu, it raises an event and blocks a certain range of cells. I got codes to lock a cell, but I can’t lock it when I select the dropdown menu. The value of the string data in the drop-down menu - 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 does not work, are there any problems with this code?

Answer 1

If you use the data validation drop-down list, you can use the Worksheet_Change event as follows:

 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 your data validation is in cell A1. You will need to update the link depending on the situation.

Answer 2

an example can be found here with named ranges:

http://www.sebastianviereck.de/en/VBA-dropdown-change-event-to-perform-action/