Userform text-boxes - automatic age calculation

AndyRoo119

New Member
Joined
Dec 26, 2013
Messages
4
Hi everyone,
My question relates to Excel 2007 VBA

I have a Userform that is used for easy entry of data from paper onto a spreadsheet.

The userform in its current state has, and still does work fine.

However, I need it now to help me to calculate the persons age from the persons Date of Birth, entered into the txtDateOfBirth' text-box, then automatically enter that age into the 'txtAge' text-box..

Seems a simple thing to do but have been, and still am going round in circles.

I've listed a copy of my working code - so far!

If someone can help it would be greatly appreciated. Thanks.


Code:
'
' UserForm for Entering data from Accident/Incident paperwork
'


' Cancel Button Code
Private Sub cmdCancel_Click()
    Unload Me
End Sub


' Clear the Form
Private Sub cmdClear_Click()


    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        End If
    Next ctl


End Sub




' OK Button Code
Private Sub cmdOK_Click()


    Dim RowCount As Long
    Dim ctl As Control
    Dim AgeCalc As Integer
    
    
    
' CHECK USER INPUT


'   Book Number Box
    If Me.txtBookNumber.Value = "" Then
        MsgBox "Please Enter a Book Number.", vbExclamation, "Book Number"
        Me.txtBookNumber.SetFocus
        Exit Sub
    End If
'   Page Number Box
    If Me.txtPageNumber.Value = "" Then
        MsgBox "Please Enter a Page Number.", vbExclamation, "Page Number"
        Me.txtPageNumber.SetFocus
        Exit Sub
    End If
'   Date Box
    If Not IsDate(Me.txtDate.Value) Then
        MsgBox "Please enter a valid date (dd mmm yy).", vbExclamation, "Date Box"
        Me.txtDate.SetFocus
        Exit Sub
    End If
'   Time Box
    If Me.txtTime.Value = "" Then
        MsgBox "Enter a valid Time (hh:mm).", vbExclamation, "Time Box"
        Me.txtTime.SetFocus
        Exit Sub
    End If
'   Date of Birth box
    If Me.txtDateOfBirth.Value = "" Then
        MsgBox "Enter the Date Of Birth (dd mmm yy).", vbExclamation, "Date Of Birth"
        Me.txtDateOfBirth.SetFocus
        Exit Sub
'
'
'   Calculate the persons age after the Date of Birth is entered
'
'


'   Client/Staff/Other Box
    If Me.cboClientStaffOther.Value = "" Then
        MsgBox "Enter either Client, Staff or Other from the list.", vbExclamation, "Client/Staff/Other"
        Me.cboClientStaffOther.SetFocus
        Exit Sub
    End If
'   Found on the Floor Box
    If Me.cboFoundOnFloor.Value = "" Then
        MsgBox "This Box must contain either No or Yes.", vbExclamation, "Found On The Floor"
        Me.cboFoundOnFloor.SetFocus
        Exit Sub
    End If
'   Cause of Incident Box
    If Me.cboCauseOfIncident.Value = "" Then
        MsgBox "Enter the Cause of Incident from the list.", vbExclamation, "Cause Of Incident"
        Me.cboCauseOfIncident.SetFocus
        Exit Sub
    End If
'   Nature of Injury Box
    If Me.cboNatureOfInjury.Value = "" Then
        MsgBox "Enter a Nature of Injury from the list.", vbExclamation, "Nature Of Injury"
        Me.cboNatureOfInjury.SetFocus
        Exit Sub
    End If
'   Outcome of Incident Box
    If Me.cboOutcomeOfIncident.Value = "" Then
        MsgBox "Enter an Outcome of the Incident from the list.", vbExclamation, "Outcome Of Incident"
        Me.cboOutcomeOfIncident.SetFocus
        Exit Sub
    End If
'   Age Box
    If Not IsNumeric(Me.txtAge.Value) Then
        MsgBox "The Age must contain a number.", vbExclamation, "Age"
        Me.txtAge.SetFocus
        Exit Sub
    End If
 '   Comments Box
        Me.txtComments.SetFocus
        
 ' Write Data to the Worksheet
    RowCount = Worksheets("AccidentsAndIncidentsData").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("AccidentsAndIncidentsData").Range("A1")
        .Offset(RowCount, 0).Value = Me.txtBookNumber.Value
        .Offset(RowCount, 1).Value = Me.txtPageNumber.Value
        .Offset(RowCount, 2).Value = DateValue(Me.txtDate.Value)
        .Offset(RowCount, 3).Value = Me.txtTime.Value
        .Offset(RowCount, 4).Value = Me.cboClientStaffOther.Value
        .Offset(RowCount, 5).Value = DateValue(Me.txtDateOfBirth.Value)
        .Offset(RowCount, 6).Value = Me.cboFoundOnFloor.Value
        .Offset(RowCount, 7).Value = Me.cboCauseOfIncident.Value
        .Offset(RowCount, 8).Value = Me.cboNatureOfInjury.Value
        .Offset(RowCount, 9).Value = Me.cboOutcomeOfIncident.Value
        .Offset(RowCount, 10).Value = Me.txtAge.Value
        .Offset(RowCount, 11).Value = Me.txtComments.Value
        ' Timestamp the data entry
        .Offset(RowCount, 12).Value = Format(Now(), "dd mmm  yy hh:mm")
    End With
 
 ' Then Clear the Form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        End If
    
    Next ctl


   
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

Maybe try:

Code:
Private Sub txtDateOfBirth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.txtDateOfBirth.Value) Then
        txtAge.Value = DateDiff("yyyy", DateValue(txtDateOfBirth.Value), Date)
    End If
End Sub
 

AndyRoo119

New Member
Joined
Dec 26, 2013
Messages
4
Hi Andrew,

Firstly can I say thanks for the welcome to Mr Excel.

And secondly, thank you so much for your very prompt and informative reply - which does exactly as I wanted.

I knew the answer would be relatively simple - but could I work it out? not in a month of Sunday's.

Once again thank you for all your time and effort.

Regards Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top