CODEWITHSUNDEEP
eventsexceltriggersvba
Ivan Lee
Ivan Lee

Reputation: 51

Trigger event when select from dropdown

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?

Upvotes: 5

Views: 63521

Answers (2)

Sebastian Viereck
Sebastian Viereck

Reputation: 5915

This will trigger an event when the named range "my_named_range" dropdown content is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkTarget As Range
    Set checkTarget = Application.Intersect(Target, Range("my_named_range"))
    If checkTarget Is Nothing Then
        Debug.Print "not active range, exit"
    Else
        MsgBox "success"
    End If

End Sub

Upvotes: 2

sous2817
sous2817

Reputation: 3960

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.

Upvotes: 5

Related Questions