Solved: TextBox: Entering Date in specific format

[shrivallabha]

I am creating a userform for Data Entry. It has many textboxes. Some of these textboxes will get Dates as their input. I want the users to fill the date in certain format. I spent a little while on searching and googling. Then came to this code. Is it the correct approach? Or there can be a better programming choice.

[VBA]Private Sub AFCActID_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If AFCActID.Value <> "" Then
If AFCActID.Value Like "#*?#*?####" Then
Else
MsgBox "Please fill date in mm/dd/yyyy format only!"
AFCActID.Value = ""
End If
End If
End Sub[/VBA]

The user can choose to leave a blank field but should not enter some other info such as text.

The problem as I see: the user can get away with entering absurd info at *(asterisk). And making it:
[VBA]"##?##?####"[/VBA]
will force the user to enter 05/05/2010 for 5/5/2010. Fat fingers won't like that. Kindly advice.

[GTO]

Do I understand correctly, that you want the user to be able to use either m/d/yyyy or mm/dd/yyyy?

[shrivallabha]

Perfect!

[GTO]

Hi Shrivallabha,

I think this would work.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Static REX As Object
    
    If REX Is Nothing Then
        Set REX = CreateObject("VBScript.RegExp")
        With REX
            .Global = False
            .MultiLine = False
            .Pattern = "^\d{1,2}/\d{1,2}/(\d{2}|\d{4})$"
            ' Change pattern to below if 2-digit year is not to be allowed.
            '.Pattern = "^\d{1,2}/\d{1,2}/\d{4}$"
        End With
    End If
    If Not TextBox1.Value = vbNullString Then
        If Not REX.Test(TextBox1.Value) Then
            Cancel = True
            MsgBox """" & TextBox1.Value & """ is invalid." & vbCrLf & vbCrLf & _
                   "Please enter like mm/dd/yy, m/d/yy, or with a four-digit year.", _
                   vbInformation, vbNullString
            TextBox1.Value = vbNullString
        End If
    End If
End Sub

Hope that helps,

Mark

[shrivallabha]

It certainly is better code. Thanks a lot Mark . It works nicely .

[GTO]

You are very welcome