User Form - DOB Validation

[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.

[Dave Peterson]

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()

Glenn wrote:
>
> 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.

--

Dave Peterson

[Glenn]

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.


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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()
>
> Glenn wrote:
> >
> > 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.
>
> --
>
> Dave Peterson

[Dave Peterson]

Chip Pearson has some code that will determine the age at:
http://www.cpearson.com/excel/datedif.htm

Look for this function:
Function Age(Date1 As Date, Date2 As Date) As String

You can use it to validate the date.

Glenn wrote:
>
> 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.
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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()
> >
> > Glenn wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Glenn]

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?


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Chip Pearson has some code that will determine the age at:
> http://www.cpearson.com/excel/datedif.htm
>
> Look for this function:
> Function Age(Date1 As Date, Date2 As Date) As String
>
> You can use it to validate the date.
>
> Glenn wrote:
>>
>> 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.
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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()
>> >
>> > Glenn wrote:
>> > >
>> > > 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.
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

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



Glenn wrote:
>
> 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?
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Chip Pearson has some code that will determine the age at:
> > http://www.cpearson.com/excel/datedif.htm
> >
> > Look for this function:
> > Function Age(Date1 As Date, Date2 As Date) As String
> >
> > You can use it to validate the date.
> >
> > Glenn wrote:
> >>
> >> 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.
> >>
> >> "Dave Peterson" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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()
> >> >
> >> > Glenn wrote:
> >> > >
> >> > > 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.
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Glenn]

Thank you very much.
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
> Glenn wrote:
>>
>> 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?
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > Chip Pearson has some code that will determine the age at:
>> > http://www.cpearson.com/excel/datedif.htm
>> >
>> > Look for this function:
>> > Function Age(Date1 As Date, Date2 As Date) As String
>> >
>> > You can use it to validate the date.
>> >
>> > Glenn wrote:
>> >>
>> >> 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.
>> >>
>> >> "Dave Peterson" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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()
>> >> >
>> >> > Glenn wrote:
>> >> > >
>> >> > > 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.
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson