Userform text-boxes - automatic age calculation

[AndyRoo119]

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.

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

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe try:

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]

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