CODEWITHSUNDEEP
excelvba
Lillian
Lillian

Reputation: 51

vba Change event when data validation selected

Hi I have a workbook that in Column C there is data validation drop down list. I'm tryinto code so that when a specific status is selected in the drown down (i.e. "Verification") Column I will automatically enter "Verification Unprocessed". Column I also has data validation and a drop down list. I've searched all over and can't seem to find a code that identifies a worksheet change event from a data validation drop down list. Thanks!

Upvotes: 0

Views: 5856

Answers (1)

BobbitWormJoe
BobbitWormJoe

Reputation: 639

You fact that you have a drop-down list is not important. The Worksheet Change event will fire regardless of how the cell is changed. All you need to do is check if the Target variable in the event is in the right column, then do what you need to do. Sample code:

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Count = 1 Then
            If .Row > 1 And .Column = 3 Then
                If .Value = "specific status" Then
                    .Offset(0, 6).Value = "Verification Unprocessed"
                Else
                    .Offset(0, 6).ClearContents
                End If
            ElseIf .Column = 5 Then
                If .Value = Date Then
                   Call Lilly
                End If
            End If
        End If
    End With

End Sub

Let me know if you need further information.

EDIT: Incorperated your original code.

Upvotes: 2

Related Questions