Calculate Exact Age in Userform

[twitch73]

Good afternoon all,

I have a userform in which in one of the txtfields, I enter the Date of Birth. I format the entry to dd.mm.yyyy

The code I am trying to use bugs out on the 4th line while trying to calculate the age and I can't seem to figure this one out.

Any help would be awesome

Please Login or Register  to view this content.

Tayler

[Ashali]

Try in the 4th line:

txtAge1.formula = Fix((DateDiff("d", txtDOB1.Value, Now) / 365.25))

[twitch73]

Sorry to say .. no go.
It Yellowed the Private sub line and blue highlighted '.Formula ='

Any other ideas?

[Ashali]

Try:

txtAge1.text = Fix((DateDiff("d", txtDOB1.Value, Now) / 365.25))

Just to be clear, you want the age to appear in text box (txtAge1) based on DOB entered in txtDOB1?

[twitch73]

nope .. tried that one myself. Doesn't work

Yes, when I enter the date of birth into txtDOB1, eg, 19.04.1973, it will calculate the exact age (39) into txtAge1.

It has to have something to do with the Format line.
If I blank out the format line, the date appears under the original code.

Which is why I said in the post that I format the value of txtDOB1 to DD.MM.YYYY

So I would assume as soon as I do, it no longer becomes a date, and becomes just text.

[twitch73]

I got .. after typing that it hit me .. reverse the code, so it does the age first, THEN formats the date.

[code]
Private Sub txtDOB1_AfterUpdate()
Set dValue = txtDOB1
txtAge1.Value = Fix((DateDiff("d", txtDOB1.Value, Now) / 365.25))
txtDOB1.Value = Format(dValue, "DD.MM.YYYY")
End Sub
[\code]

Thanks for helping me brainstorm

Tayler

[Ashali]

Great! Learnt something new myself

[stojko89]

That was it? Lol....guess it has to be like that for the code to work in excel 2003 in excel 2007 it works fine both ways