birthdate in textbox to age in lable on userform

  • Thread starter Thread starter sybmathics
  • Start date Start date
S

sybmathics

Hi,

I have tried everything in my knowledge to show the age of a person, whose
birthday is entered in a textbox on a userform, in a lable next to the
textbox.

I just end up with messages like: "no object defined" and "type mismatch"
even when i have declared the textbox value or text, date etc and asigned
this to variable bd (you know "birthday").

In Access it is really simple, but I can't seem to get the code in Excel.

Please help.

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

I'm gonna take one of his functions and plop it into my workbook's project.

But I created a small form with a commandbutton, a textbox and a label on it.

I put this behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox1.Value) Then
Me.Label1.Caption _
= Age(CDate(Me.TextBox1.Value), Date)
Else
Me.Label1.Caption = "Invalid entry"
End If
End Sub

Then I put Chip's Age function in a General module:

Option Explicit
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
 
Hello Sybolt,

Here is an example of calculating the the person's age from the
birthdate entered into a TextBox and placing their age in a Label.

EXAMPLE:


Code:
--------------------
Dim Age
Dim Birthday As Date

BirthDay = CDate(TextBox1.Text)
Age = Year(Date) - Year(Birthday)

Label1.Caption = Age
 
Back
Top