Show Age and Age Range from Date of Birth entered into userform

  • I am putting together a userform and am having issues with some text box's, I would like to solve the following issue. I am using Excel 2013

    I have trawled through the forum and can't seem to find an answer, so I will apologise if this has been asked before

    On the userform I have created three text boxes

    "TxDoB " text box with Date of Birth value

    In "TxAge" - I would like this to show age as an integer, displayed as a whole number, just as you would for your age, so roounded down rather than rounded up

    In "TxRange" I would like to display an age range i.e "18-21" or "22-29" or "30-39" etc

    Entering the date of birth is no issue as below

    Private Sub TxDoB_AfterUpdate()
    Dim dDate As Date
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TxDoB.Value = Format(TxDoB.Value, "d mmm yy")
    On Error GoTo 0
    ' updates Age and populate Age & Age Range textbox's
    End Sub

    I have a cell on a worksheet Sheet6.Range("D1") which displays today's date ie NOW() formatted as "D MMM YY"

    So from this I am populating "TxAge" using the formula below

    Private Sub TxAge_Change()
    Dim As Integer
    On Error Resume Next
    TxAge.Value = ((Sheet6.Range("D1").Value) - (TxDoB.Value) / 365.25)
    On Error GoTo 0
    End Sub

    First question is how do I get the value in "TxAge" to display as a whole number, rounded down.

    Second question is I want the value in "TxAge" to populate "TxRange", I can do it in a long winded way


    Can I simplify this to populate "TxRange" with the following values







    Thanks in advance if this sounds like a silly question


  • One way to do this within the same block of code is as follows:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!