Place Age in Active Sheet's Cell

[adam]

The following code places the age in text box txtAge when the date of birth is written in text box txtDateofBirth of the active userform.

Code: Select all

Private Sub txtDateOfBirth_AfterUpdate()
Dim Period As String
    
    If Not IsDate(Me.txtDateofBirth.Value) Then
        MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
        Me.txtAge = ""
    Else
        Me.txtAge = AgeCalc(CDate(Me.txtDateofBirth), Period) & Chr(32) & Period
    End If
End Sub
    
Function AgeCalc(DOB As Date, Period As String) As Long
    
    Select Case Date - DOB
    Case Is > 365
        AgeCalc = Int((Date - DOB) / 365)
        Period = IIf(AgeCalc >= 2, "Years", "Year")
        
    Case Is > 30
        AgeCalc = Int((Date - DOB) / 30)
        Period = IIf(AgeCalc >= 2, "Months", "Month")
    Case Is > 7
        AgeCalc = Int((Date - DOB) / 7)
        Period = IIf(AgeCalc >= 2, "Weeks", "Week")
    Case Else
        AgeCalc = Date - DOB
        Period = IIf(AgeCalc >= 2, "Days", "Day")
    End Select
End Function

How should the code be changed, so that it places the age in cell "I14" when the date of birth is written in cell "K9" of the active sheet; instead of the above mentioned text boxes.

Any help on this wold be kindly appreciated.

Thanks in advance.

[HansV]

You should use the Worksheet_Change event in the worksheet module (right-click the sheet tab and select View Code from the popup menu to display the worksheet module).
You have already seen many examples of creating code for the Worksheet_Change event.

[adam]

Based Upon your suggestion; Here is my try.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Period As String
    
    If Not IsDate(Target, Range("K9")) Then
        MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
        Range("I14") = ""
    Else
        Range("I14") = AgeCalc(CDate(Range("K9")), Period) & Chr(32) & Period
    End If
End Sub
    
Function AgeCalc(DOB As Date, Period As String) As Long
    
    Select Case Date - DOB
    Case Is > 365
        AgeCalc = Int((Date - DOB) / 365)
        Period = IIf(AgeCalc >= 2, "Years", "Year")
        
    Case Is > 30
        AgeCalc = Int((Date - DOB) / 30)
        Period = IIf(AgeCalc >= 2, "Months", "Month")
    Case Is > 7
        AgeCalc = Int((Date - DOB) / 7)
        Period = IIf(AgeCalc >= 2, "Weeks", "Week")
    Case Else
        AgeCalc = Date - DOB
        Period = IIf(AgeCalc >= 2, "Days", "Day")
    End Select
End Function
End Function

But I'm getting debug message by highlighting the line

Code: Select all

 If Not IsDate(Target, Range("K9")) Then

What have I done wrong here?

[HansV]

IsDate has only one argument.

[adam]

Thanks for the reply. I would be happy if you could give a detail answer of how to do that?

Thanks in advance.

[HansV]

See IsDate Function - Excel - Microsoft Office.

[adam]

Thanks for the recommendation. I guess I've figured that out.