Userform Date validation

  • Thread starter Thread starter David Goodall
  • Start date Start date
D

David Goodall

Hi,
I'm currently developing a userform which has a text box that prompts the
user for a date of birth. I would like to validate the input as a correct
date of birth. ie not born before 1900 and in correct dd/mm/yyyy format.

I've had a look at the the isdate function but I'm not sure if it can handle
the British date format.

Any help would be greatly appreciated.

David
 
Some pointers:

AFAIK IsDate should adapt itself to the current regional settings so you
will be ok with this.

You can use the Year function to extract just the year part of a date after
calling IsDate to ensure it is actually a date.

Be aware that things like IsDate("2/2003") will return True. Depending on
your situation this may or may not be desirable and could require further
refinement.



--
Regards,


Bill Lunney
www.billlunney.com
 
David;

Because of being in a hurry just a brief and not complete answer to your "
problem".

A part of your "check" can be something like this.
Here you check for a genuine dd/mm/yyyy dateformat.
Assuming your textbox is called tbStartDate.

If Not IsDate(.tbStartDate.Value) Then
Response = MsgBox(ErrNoStartdate, vbCritical + vbOKOnly, Title)
.tbStartDate.Value = "01/01/0001 ?"
.tbStartDate.SetFocus
Exit Sub
End If

Succes;
The other part will be solved by other members I hope.
Mark Rosenkrantz.


More Excel ? www.rosenkrantz.nl or (e-mail address removed)
 
In fact, here it is
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Not ValidDate(.Text) Then
MsgBox "Please supply a valid date in dd/mm/yyyy format",
vbCritical + vbOKOnly
Cancel = True
End If
End With
End Sub

Function ValidDate(dte As String)
If Len(dte) <> 10 Or Mid(dte, 3, 1) <> "/" Or Mid(dte, 6, 1) <> "/" Then
ValidDate = False
Exit Function
End If
If Mid(dte, 4, 2) > 12 Then
ValidDate = False
Exit Function
End If
If CLng(Right(dte, 4)) < 1900 Then
ValidDate = False
Exit Function
End If
ValidDate = True
On Error GoTo invalid_date
IsDate (CDate(dte))
Exit Function
invalid_date:
ValidDate = False
End Function
 
Hi David

In my opinion users should never be bothered with proper input formats and annoying messageboxes.

The code below formats and validate all common date inputs and converts to the desired format without noise and interruptions. In
addition to all date entries that regional settings recognize, you can use
Jan 12
and 4 to 8 digit non-delimited numbers (in european ddmmyyyy order) like
1201
120195
12011995

Here's the code, I use it all the time in my projects. Edit/remove the last age check part, it's a demo for you only:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim iDay As Integer
Dim iMonth As Integer
Dim iYear As Integer
Dim dtDate As Date

With TextBox1
If Val(.Text) >= 1000000 Then
iDay = Int(Val(.Text) / 1000000)
iMonth = Int(Val(.Text) / 10000) Mod 100
iYear = Val(.Text) Mod 10000
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
ElseIf Val(.Text) > 10000 Then
iDay = Int(Val(.Text) / 10000)
iMonth = Int(Val(.Text) / 100) Mod 100
iYear = Val(.Text) Mod 100
If iYear > 30 Then
iYear = iYear + 1900
Else
iYear = iYear + 2000
End If
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
ElseIf Val(.Text) > 100 Then
iDay = Int(Val(.Text) / 100)
iMonth = Val(.Text) Mod 100
iYear = Year(Date)
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
Else
If IsDate(.Text) = False Then
.Text = ""
Else
dtDate = DateValue(.Text)
If dtDate < 10 Then
.Text = ""
Else
.Text = Format$(dtDate, "dd/mm/yyyy")
End If
End If
End If
End With
'edit / remove from here:
If dtDate > 1000 Then
Select Case Year(Date) - Year(dtDate)
Case 0 To 13
'replace with proper actions:
MsgBox "too young"
Case 80 To 999
MsgBox "too old"
Case Else
End Select
End If
End Sub
 
Nice stuff. However, I noticed a good feature in this
routine. If one was to provide an input like 151560 in
the textbox, then this routine "correctly" interprets it
as 15th day of 15th month of the year 1960. And since
there are only 12 months in 1960, the 13th month is
considered as January 1961, 14th month as February 1961,
and 15th month as March 1961.

Thus, to make matters short, 151560 results in 15/03/1961
as output of this routine.

-----Original Message-----
Hi David

In my opinion users should never be bothered with proper
Click to expand...
input formats and annoying messageboxes.
The code below formats and validate all common date
Click to expand...
inputs and converts to the desired format without noise
and interruptions. In
addition to all date entries that regional settings recognize, you can use
Jan 12
and 4 to 8 digit non-delimited numbers (in european ddmmyyyy order) like
1201
120195
12011995

Here's the code, I use it all the time in my projects.
Click to expand...
Edit/remove the last age check part, it's a demo for you
only:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim iDay As Integer
Dim iMonth As Integer
Dim iYear As Integer
Dim dtDate As Date

With TextBox1
If Val(.Text) >= 1000000 Then
iDay = Int(Val(.Text) / 1000000)
iMonth = Int(Val(.Text) / 10000) Mod 100
iYear = Val(.Text) Mod 10000
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
ElseIf Val(.Text) > 10000 Then
iDay = Int(Val(.Text) / 10000)
iMonth = Int(Val(.Text) / 100) Mod 100
iYear = Val(.Text) Mod 100
If iYear > 30 Then
iYear = iYear + 1900
Else
iYear = iYear + 2000
End If
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
ElseIf Val(.Text) > 100 Then
iDay = Int(Val(.Text) / 100)
iMonth = Val(.Text) Mod 100
iYear = Year(Date)
dtDate = DateSerial(iYear, iMonth, iDay)
.Text = Format$(dtDate, "dd/mm/yyyy")
Else
If IsDate(.Text) = False Then
.Text = ""
Else
dtDate = DateValue(.Text)
If dtDate < 10 Then
.Text = ""
Else
.Text = Format$(dtDate, "dd/mm/yyyy")
End If
End If
End If
End With
'edit / remove from here:
If dtDate > 1000 Then
Select Case Year(Date) - Year(dtDate)
Case 0 To 13
'replace with proper actions:
MsgBox "too young"
Case 80 To 999
MsgBox "too old"
Case Else
End Select
End If
End Sub
--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"David Goodall" <[email protected]> skrev i
Click to expand...
melding win.server.ntli.net...
 
I have seen this discussion frequently and because of it have opted to go
for a different solution. Not having a calandar control, I use 3
comboboxes -
one for day, one for month, and one for year. Than the code combines the
input into a date.

steve
 
Back
Top