Date Format on Form
 

Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid date.
How do I check to make sure it is a valid date? Do I have to get the system
settings, then compare the entered value to the system settings? If so how
do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

Date Format on Form
 

most vba functions like cdate and sfromat are localized
so try like:


Private Sub TextBox1_BeforeUpdate( _
ByVal Cancel As MSForms.ReturnBoolean)
Dim dtMin As Date, dtMax As Date

'set the min/max valid dates
dtMin = DateSerial(Year(Date) - 1, Month(Date), _
Day(Date) + 1)
dtMax = Date

With TextBox1
If Len(.Text) = 0 Then
Exit Sub
ElseIf IsDate(.Text) Then
If CDate(.Text) = dtMin And CDate(.Text) <= dtMax Then
If Format(CDate(.Text), vbShortDate) < .Text Then
.Text = Format(CDate(.Text), "Short Date")
End If
Else
Cancel = True
End If
Else
.Text = ""
Cancel = True
End If
End With
If Cancel Then
MsgBox "Valid dates are between " & vbLf & Format(dtMin, _
"Short Date") & " and " & Format(dtMax, "Short Date"), _
vbInformation
End If



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brad wrote :

Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid
date. How do I check to make sure it is a valid date? Do I have to
get the system settings, then compare the entered value to the system
settings? If so how do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

Date Format on Form
 

Brad,

If the entered value could in anyway be considered a date, then this will indicate it. Give it a
try:

If IsDate(UserForm1.TextBox1.Text) Then
MsgBox "It's a date: " & _
Format(CDate(UserForm1.TextBox1.Text), "mmm dd, yyyy")
Else
MsgBox "It isn't a date!"
End If

HTH,
Bernie
MS Excel MVP


"Brad" wrote in message
...
Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid date.
How do I check to make sure it is a valid date? Do I have to get the system
settings, then compare the entered value to the system settings? If so how
do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

Date Format on Form
 

Dim dt as Date
On Error Resume Next
dt = cdate(Userform1.Textbox1.Text)
On Error goto 0
if dt < 0 then
' other validation checks if it can't be any date

--
Regards,
Tom Ogilvy



"Brad" wrote in message
...
Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid date.
How do I check to make sure it is a valid date? Do I have to get the
system
settings, then compare the entered value to the system settings? If so
how
do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

Date Format on Form
 

ALL very good solutions.

Thanks.

I'm going to give them all a try.

Thanks!

Brad

"Brad" wrote:

Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid date.
How do I check to make sure it is a valid date? Do I have to get the system
settings, then compare the entered value to the system settings? If so how
do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

Date Format on Form
 

You might want to take a look at Ron de Bruin's site for some tips/code/free
calendar control:
http://www.rondebruin.nl/calendar.htm

Brad wrote:

Thanks for taking the time to read my question.

I have a field on a form in which I want the user to enter a valid date.
How do I check to make sure it is a valid date? Do I have to get the system
settings, then compare the entered value to the system settings? If so how
do I do that?

For example, under Control Pannel | Regional and Language Options |
Customize | Date : Short Date format

Compare this to the value entered on the form.

Are there easier ways?

Thanks,

Brad

--

Dave Peterson