User Form - DOB Validation

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I am creating a user form. One of the fields is for the date of birth of
the person being entered.

The person being entered has to be between 14 and 16 on the date of entry.

Is there a way to set up some type of validation so that if the person isn't
between 14 and 16 an error message will pop up?

If so, how?

Thanks.
 
There's a function called =datedif() that you can use.

Select your cell (I used A1).
Data|Validation
Custom
formula:
=AND(DATEDIF(A1,TODAY(),"y")>13,DATEDIF(A1,TODAY(),"y")<17)

First, take a look at Chip Pearson's notes at:
http://www.cpearson.com/excel/datedif.htm

For lots more info on =datedif()
 
Thanks, but that isn't quite what I'm looking for .

I am creating a User Form, and I want for the date to be validated when the
user clicks the command button to add the record to the database.

For instance, I know how to check for missing data in a field on the form
and have an error pop up saying something like "Please enter a name", but I
can't figure out how to make it calculate the age and if less than 14 or
greater than 16 to tell the user they are using the wrong form.
 
Ok, I found this code:

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function


But it doesn't really say how/where to put that into the VBA, or how to make
the pop up box come up if the person is between 14 and 16.

Any suggestions?
 
I created a small userform with a textbox, a label and two buttons.

This is the code that I had in behind the form:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim okToContinue As Boolean
Dim myAge As Long

Me.Label1.Caption = ""

okToContinue = True
If IsDate(Me.TextBox1.Value) Then
'keep looking
Else
okToContinue = False
End If

If okToContinue = False Then
Me.Label1.Caption = "Please check Date!"
Exit Sub
End If

myAge = Age(CDate(Me.TextBox1.Value), Date)
If myAge > 16 _
Or myAge < 14 Then
Me.Label1.Caption = "Not right age: " & myAge
Else
Me.Label1.Caption = "Woohoo"
'keep going to add the stuff to the worksheet
End If

End Sub

I wanted to return just the year from Chip's Age routine. This was the slightly
modifed code that was placed in a general module.

Option Explicit
Function Age(Date1 As Date, Date2 As Date) As Long 'String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
End If
Age = Y '& " years " & M & " months " & D & " days"
End Function


Ok, I found this code:

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

But it doesn't really say how/where to put that into the VBA, or how to make
the pop up box come up if the person is between 14 and 16.

Any suggestions?
Click to expand...
 
Thank you very much.
Dave Peterson said:
I created a small userform with a textbox, a label and two buttons.

This is the code that I had in behind the form:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim okToContinue As Boolean
Dim myAge As Long

Me.Label1.Caption = ""

okToContinue = True
If IsDate(Me.TextBox1.Value) Then
'keep looking
Else
okToContinue = False
End If

If okToContinue = False Then
Me.Label1.Caption = "Please check Date!"
Exit Sub
End If

myAge = Age(CDate(Me.TextBox1.Value), Date)
If myAge > 16 _
Or myAge < 14 Then
Me.Label1.Caption = "Not right age: " & myAge
Else
Me.Label1.Caption = "Woohoo"
'keep going to add the stuff to the worksheet
End If

End Sub

I wanted to return just the year from Chip's Age routine. This was the
slightly
modifed code that was placed in a general module.

Option Explicit
Function Age(Date1 As Date, Date2 As Date) As Long 'String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
End If
Age = Y '& " years " & M & " months " & D & " days"
End Function
Click to expand...
 
Back
Top