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


    Code
    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
    TxAge_Change
    ' 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


    Code
    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
    TxRange_Change
    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


    i.e


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

    "18-21"

    "22-29"

    "30-39"

    "40-49"

    "50-59"


    etc


    Thanks in advance if this sounds like a silly question


    IainC

  • 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!