Worksheet Change Event Triggering With Cell Validation "Cancel"

[Ark68]

I have a column (A) of cells for which I have a data validation rule applied. As it is, if the user enters an invalid value into a cell in that column, the user is able to select "Retry, Cancel" or "Help".
I also have code set up so that if the value in any of the cells in column A change, code is triggered. I'm finding, that if the user enters an erroneous value, trapped by the validation rule, and the user selects cancel, that it triggers the worksheet change event for the change of that target.

How can I prevent cancel from triggering the code?

Sub Worksheet_Change(ByVal Target As Range)
    Dim cval As String, aval As String
    Dim bval As String
    Dim msg1 As String, msg2 As String, msg3 As String
    Dim acnt As Long
    If Not Application.Intersect(Columns(1), Range(Target.Address)) Is Nothing Then
Stop
        aval = "R" & Target.Value
        acnt = Application.WorksheetFunction.Match(aval, ws_pdata.Columns(1), 0)
        If acnt > 1 Then
            MsgBox "permit already exists in database."
            Exit Sub
        End If
    ElseIf Not Application.Intersect(Columns(3), Range(Target.Address)) Is Nothing Then
Stop
        cval = Target.Value
        
    ElseIf Not Application.Intersect(Columns(2), Range(Target.Address)) Is Nothing Then
    . . . .
    End If
End Sub

 

[rlv01]

How can I prevent cancel from triggering the code?

So long as events are enabled, you can't. The change event is triggered on any change to the cell, even a wrong one that the validation rules reject. What you have to do is add some code to Sub Worksheet_Change to intercept that condition. For example, selecting cancel will clear the bad entry from the cell, so something like this might work for you.

    If Not Application.Intersect(Columns(1), Range(Target.Address)) Is Nothing Then
        If IsEmpty(Target) Then
            Exit Sub
        End If