Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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
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.
Rich (BB code):
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