The following code is a routine we frequently use in our Excel projects to verify that the user has entered a date properly within a text box in a userform. In addition, dates such as 0704 get transformed to 07/04/yy where yy is the current year. Valid date inputs include 0704, 070412, 07/04/12, or 07/04/2012. All four will be validated and transformed to 07/04/12.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Function CheckDate(ctrl As Control) With ctrl strMonth = Left(.Value, 2) strDay = Mid(.Value, 3, 2) If Len(.Value) = 4 Then strYear = Year(Now()) Else strYear = Right(.Value, 2) End If On Error Resume Next strDate = DateValue(strMonth & "/" & strDay & "/" & strYear) On Error GoTo 0 If IsDate(strDate) Then .Value = Format(strDate, "mm/dd/yy") CheckDate = False ElseIf IsDate(.Value) Then .Value = Format(.Value, "mm/dd/yy") CheckDate = False ElseIf .Value = "" Then 'Do nothing CheckDate = True Else MsgBox "You entered an invalid date!", vbExclamation, "Caution!" .SetFocus CheckDate = True End If End With End Function |
