• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Worksheet Change Event With Data Validation

Deepak

Excel Ninja
Hi,

While doing some excelling i came to this question which had also puzzled me many times.

Can i have your valuable feedback on the same.

Why the multiple event [How may times tried to wrong entry in a DV cell before actual] is raised since the cell is not actually changed.

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 all events get processed.

Ref Q - http://stackoverflow.com/questions/36987845/data-validation-and-worksheet-change-event
 
From what I can tell... you had the right idea.

It actually commits change when you type invalid entry.
Then Data Validation error handler runs (taking precedence over VBA code. The event code is already triggered at this stage but non of the code line is executed).

Error handler then commits info again when "Retry" or "Cancel" is selected (thus triggering the event for second time and runs first pass and second pass successively when "Cancel" is selected. Queues again when "Retry" is selected).

Thus when "Cancel" is hit there's total of two events. But code runs only after Data Validation error handler completes. Therefore showing MsgBox successively after "Cancel" is hit.

You can test this by using Timer to track when code runs.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print Time
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    MsgBox Range("A1").Value

End Sub

And since Error handler for Data Validation takes place prior to any of code line being executed, typical method of disabling Events during code does not work in this instance.

Edit: For clarity and grammar.
 
Last edited:
Yes, This happens...

I think it's by design with xl.
Is there any workaround for the same?
I can't figured out any workaround yet.
 
I think best workaround is to have invisible ComboBox on the sheet and call that up for data entry (make it visible and align with the cell) and validate entry before it's committed to the cell.

I prefer to use BeforeDoubleClick event for this purpose. But you can probably use SelectionChange.

Let me see if I can remember the workbook that I did.
 
Found it. The validation check is done on TempComboBox_LostFocus event.

Following function in regular module is used to check if match exist within named range.

Code:
Function cboValidate(lstName As String, selVal As String) As Boolean
    Dim nRange As Range
    Set nRange = Range(lstName)
    cboValidate = IIf(IsError(Application.Match(selVal, nRange, 0)), False, True)
End Function

Bulk of codes are based on method outlined in Contexture website.
http://www.contextures.com/xlDataVal11.html
 

Attachments

  • Example (1).xlsb
    174.2 KB · Views: 24
I like ur approach but somehow it's not always possible.
I have managed a workaround. Pls have a look.
 

Attachments

  • DV Check_WS Change Event.xlsm
    16.2 KB · Views: 46
Back
Top