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.

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

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:
"##?##?####"
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 :friends: . It works nicely :bow: .

[GTO]

You are very welcome:beerchug: