Validate a date

[Melker]

Hi,


I would like to validate that something that the user have writen in a
text-box in a user form realy is a date and that is has a proper format.
Does any one have any ideas ?


Thank you !

Melker

 

[Harald Staff]

Hi Melker

A user should really be allowed to enter a date in whichever format she's
comfortable with; Excel and VBA can understand most of them and a date is a
date is a date.

Try this, it runs when the textbox looses focus. It accepts "Feb 1", "2.5
03" and whatever, if it's a valid date then it reformats to mm/dd/yyyy
format, otherwise it clears and beeps:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Dt As Date
On Error Resume Next
Dt = DateValue(TextBox1.Text)
If Dt > DateSerial(1950, 1, 1) Then
TextBox1.Text = Format$(Dt, "mm/dd/yyyy")
Else
TextBox1.Text = ""
Beep
End If
End Sub

HTH. Best wishes Harald

 

[Melker]

Thank you,

I will try that!

I would however want to see if i can not also "test" the date.
With that i mean that 2004-02-31 or 31/2/2004 would not be valid...

Is there a easy way ?


Regards

Melker

 

[Harald Staff]

Tests first, complaints later ;-)

Ok, we may have different behaviors caused by international date formatting
differences, that is a pretty complex issue. But here in Norway february
31st is invalid to my code and the entry is discarded. Likewise, feb 29 2000
is accepted but feb 29 2100 isn't. You ought to be pretty safe.

HTH. Best wishes Harald

 

[Melker]

Great,
Jag provar det !

Tusen tack !

Melker

Tests first, complaints later ;-)

Ok, we may have different behaviors caused by international date
formatting
differences, that is a pretty complex issue. But here in Norway february
31st is invalid to my code and the entry is discarded. Likewise, feb 29
2000
is accepted but feb 29 2100 isn't. You ought to be pretty safe.

HTH. Best wishes Harald