Dec 22

Verifying Dates within Excel UserForms

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.

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

 

Lauren Lummus