TEXTBOX - VALIDATE DATE INPUT

[Guest]

I want to ensure that an input to a textbox in a userform is in date format
either dd/mm/yy or dd mmm yy. Can anyone help ?

 

[Chip Pearson]

Try something like


Dim S As String
S = InputBox("Enter A Date")
If S = Format(S, "d/m/yy") Or S = Format(S, "d mmm yy") Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[William Benson]

Chip, I believe Steve was looking to validate entry in a textbox not through
a prompt. So I went to work on it, and tested this with a textbox which had
18/5/05 in it (May 18, 2005 written in the format Steve desired). I used the
immediate window to test, and with these results:

1) ?Typename(textbox1.value) Ans: string (good - expected)
2) ?Format(textbox.Value, "d/m/yy") Ans: 5/5/18 (bad - not expected)
Excel converted the textbox value into the wrong date because the format
d/m/yy is not innate. Therefore Textbox1.value can never =
Format(textbox.Value, "d/m/yy").

Do you have time to comment?

Bill

 

[Dick Kusleika]

Steve

I use the BeforeUpdate event like this

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

With Me.TextBox1
If IsDate(.Text) Then
.Text = Format(.Text, "dd mmm yy")
Me.Label1.Caption = ""
Else
Cancel = True
Me.Label1.Caption = "Date required"
End If
End With

End Sub

It allows the user to enter the date in any format they like, but changes it
to your format. If they don't enter a valid date, it doesn't let them leave
the textbox and displays a message in an adjacent label that the entry isn't
valid.