Data Validation and Worksheet Change Event

Question

I use a VBA macro to query a database and build a list of available projects when a workbook is opened using the workbook activate event. I have project numbers and project names that are combined into two separate data validation lists and applied to two cells. The worksheet change event tests for changes in these cells, splits their data validation lists into arrays, and chooses the corresponding project information from the other array. For instance, if I pick a project number, the worksheet change event finds the project number's position in the project number array, and then picks the project's name from the name array based on position.

This works perfectly whenever a value is selected from the drop down, but I run into problems when values outside the list are entered. For instance, if I enter a blank cell I may get the data validation error or I may get a type mismatch when I use match to find the entered value in the array. I have an error handler to handle the type mismatch, but I would like the data validation error to trigger every time instead. Another problem is that Events will sometimes be disabled. This is much more serious because users will not have a way to turn these back on.

On top of this, I cannot figure out where or how this is happening. I can't replicate how the Events are disabled using breaks because duplicating the steps that lead to the events being disabled with breaks in place only leads to my error handler. However, when breaks aren't applied, the error handler will sometimes fail to trigger and the events will be disabled. Since I'm disabling events just before I parse arrays, I'm thinking the worksheet change fails at the Loc=Application.Match(Target.Text, NumArr, 0) - 1 line, but I can't figure out why no error would be triggered. At the very least, I should get a message with the error number and description, and events should be re-enabled.

Can anyone advise on the interaction between worksheet change and data validation? What is the call order here? Any other advice? Anything I'm missing?

ETA: I've Googled this, but I haven't found anything that helps. Everything that comes up is about working the data validation into worksheet change, nothing about the interaction or call order.

ETA #2: After trying the experiment in the answer below (Thanks Gary's Student), this gets a little more odd. If I choose "Retry" and choose the old, default value, I get the old value three times. If I hit delete, I get a space in the message box, but only one message box. Then the cell is left blank. I can put DV into a loop by clicking "Retry" and accepting the space. The DV error will come up until I click cancel. Then I will get a series of empty text message boxes, one for each time I retried the empty cell. If I start off with a listed value, clear the cell with backspace, click "Retry," and try to select another value, the worksheet change event fails at Intersect 3 times. I think the answer below sheds more light on what is going on, but it does bring up more questions also.

Here is the code I have:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NumArr() As String
Dim ProjArr() As String
Dim Loc As Integer

On Error GoTo ErrHandler:

If Target.Address = "$E$4" Then
    'Disable events to prevent worksheet change trigger on cell upates
    Application.EnableEvents = False

    'Parse validation lists to arrays
    NumArr = Split(Target.Validation.Formula1, ",")
    ProjArr = Split(Target.Offset(1, 0).Validation.Formula1, ",")

    'Change error handler
    On Error GoTo SpaceHandler:

    'Determine project number location in array
    Loc = Application.Match(Target.Text, NumArr, 0) - 1

    'Change error handler
    On Error GoTo ErrHandler:

    'Change cell value to corresponding project name based on array location
    Target.Offset(1, 0) = ProjArr(Loc)

    'Unlock cells to prepare for editing, reset any previously imported codes
    Range("C8:G32").Locked = False

    'Run revenue code import
    RevenueCodeCollector.ImportRevenueCodes

    'Re-enable events
    Application.EnableEvents = True

End If

If Target.Address = "$E$5" Then

    Application.EnableEvents = False

    NumArr = Split(Target.Validation.Formula1, ",")
    ProjArr = Split(Target.Offset(-1, 0).Validation.Formula1, ",")
    Loc = Application.Match(Target.Text, NumArr, 0) - 1
    Target.Offset(-1, 0) = ProjArr(Loc)

    Range("C8:G32").Locked = False
    RevenueCodeCollector.ImportRevenueCodes
    Application.EnableEvents = True

End If

Exit Sub

ErrHandler:
MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
Exit Sub

SpaceHandler:
MsgBox "Pick a project from the dropdown.", vbOKOnly, "Error"
Application.EnableEvents = True

End Sub

Answer 1

You have a very open-ended question...........not having the time to do a full whitepaper, here is a simple experiment. I use the Event code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, rINT As Range

    Set A1 = Range("A1")
    Set rINT = Intersect(A1, Target)
    If rINT Is Nothing Then Exit Sub
    MsgBox A1.Value
End Sub

and in A1, I setup DV as follows:

If I use the drop-down, I get the value entered and I also get the MsgBox. However, if I click on the cell and type some junk what happens is:

1. the DV alert occurs and I touch the CANCEL Button
2. I get 2 MsgBox occurrences, each with the original contents rather than the attempted junk !!

I have absolutely no idea why the event is raised since the cell is not actually changed, let alone why the Event is raised twice !! It is almost as if the event is raised on junk entry, but the DV alarm has precedence, the DV reverse the entry and another event is raised, and finally both events get processed.

Hopefully a person smarter than me will chime in.