User Entered Value Slipping past Error Checking Code

[Ark68]

Hi have this code in my worksheet module. The change event triggers after the user enters a value in cell M1 of worksheet ws_master.
The data that is required to be entered is a date. The change event code includes error checking code toensure the user enters a date.

With ws_master
        If Not Application.Intersect(.Range("M1"), Range(Target.Address)) Is Nothing Then
        
            'check for valid date
            If IsDate(.Range("M1").Value) = False Then
                Stop
                mbevents = False
                unlocked '.Unprotect
                .Range("M1").Value = "Enter valid date (month/day)"
                .Range("M1").Font.Color = RGB(192, 57, 43)
                .Range("M1").Select
                locked '.Protect
                mbevents = True
                Exit Sub
            'invalid date
            Else
                unlocked '.Unprotect
                With .Range("M1")
                    .Font.Color = vbBlack
                    .Font.Italic = False
                    .Font.Bold = True
                    .Interior.ColorIndex = 0
                End With
                RemoveCellSelectionBox
                locked '.Protect
                inq_date = ws_master.Range("S1") 'inquiry date (public)
                .... more code

In my testing, I discovered that if the user types in "207/12" into M1, the codes treats the value as valid (bypasses the code to deal with an improper date), and errs ('Type mismatch") at the line in red (as it's not really a date).

Is my error checking routine inadequate? Any suggestions on how i can improve on it to ensure a value like 207/12 is trapped?





207/12

 

[igold]

See if this helps... Written by John Walkenbach

Excel Articles - dummies

Hundreds of Excel-lent articles on how to do just about anything with Microsoft's legendary spreadsheet software. Analyze, automate, calculate, visualize, and a lot, lot more.

www.dummies.com

 

[Ark68]

Thank igold. That article did explain clearly that IsDate isn't always the most obvious solution to checking whether a value entered is a date LOL. It appears I have to go back and try and find another solution to check to see if the value entered is indeed a valid date without using IsDate.

I'll do some Googling, but in the meantime ... anyone have any suggestions of an efficient way of error checking dates?

 

[igold]

You are welcome, I was happy to help. Thanks for the feedback.