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