Excel VBA Userform to Calculate Age and BMI

http://www.onlinepclearning.com Excel VBA Userform to Calculate Age and BMI.

In this tutorial I will demonstrate how to perform Calculations in a Userform.

We will be calculating the age and the BMI from the values entered into the userform

This Video Published Since or about 4 years ago ago, Hosted by youtube.com and Published by Channel: Online PC Learning

Excel VBA Userform to Calculate Age and BMI's Video From Online PC Learning have lenght about 9:00 and was viewed more than 9398 and is still growing

BMI and Age Calculator Step by Step

Adding and creating the Worksheet formulas

Note: once you add these formulas to the worksheet click on the cell but confirm that you simply paste the formula into the formula bar at the highest , then hit enter.

This formula will calculate the age difference between the date added and today.
Formula for Cell G8
=DATEDIF(F8,TODAY(),"Y")&" Years, "&DATEDIF(F8,TODAY(),"YM")& " Months, "&DATEDIF(F8,TODAY(),"MD")&" Days"
Here is the formula to calculate our BMI from the values that have been sent from the userform.
Formula for Cell H11
=G11/((F11/100)*(F11/100))

Formulating the User form

Make sure that you simply have watched the video and make the User form as shown within the illustration above. Notice the names for the textboxes and command buttons.

You will got to use these exact names for the code that you simply are going to be later adding to the User form to figure .

This User form thus consists of

6 labels
5 text boxes
2 command buttons

Properties for the Userform

Note: There are a few of things that you simply can do with the User form which will make it user friendly.

1. Name it appropriately (does not matter what name you select but something like frmAge would be suitable.

2. Add a caption to explain what action this Userform are going to be achieving. like “Age and BMI Calculator”

Adding the VBA code to the Userform

Here is that the code for the Userform. Double click inside the Userform then copy the code below and paste into the Visual Basic editor.

Option Explicit
Private Sub cmdCalculate_Click()
'Declare the variables
Dim AddAge As Long
Dim MyAge As Range
Dim BMI As Range
'Set the the result range
Set MyAge = Sheet1.Range("G8")
'Variable for result
Set BMI = Sheet1.Range("H11")
'Check for DOB value
If IsDate(Me.txtDOB.Value) And Me.txtDOB.Value <> 0 Then
'Add date of birth to worksheet
Sheet1.Range("F8") = Me.txtDOB.Value
'Show formula result in a message box
Me.txtAge.Value = MyAge.Value
Else
MsgBox "You need to add a proper date"
txtDOB.Value = ""
Exit Sub
End If
'Check for Height value and add value
If IsNumeric(txtHeight) And txtHeight <> 0 Then
Sheet1.Range("F11") = Format(Me.txtHeight.Value, "###0.0")
Else
MsgBox "You must add a Height"
Exit Sub
End If
'Check for Weight value and add value
If IsNumeric(txtWeight) And txtWeight <> 0 Then
Sheet1.Range("G11") = Format(Me.txtWeight.Value, "###0.0")
Else
MsgBox "You must add a Weight"
Exit Sub
End If
'Show the BMI result
Me.txtBMI = BMI
Me.txtBMI.Value = Format(BMI, "#,##0.0")
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Run the Userform

In the Assorted module to your VBA editor add the code below.
Right Click the form on the worksheet then choose Assign Macro and assign the macro below. this may call the user form.

Sub Showme()
frmAge.Show
End Sub

In conclusion

I hope you've got enjoyed this tutorial to point out how you'll do calculations during a User form quite simply. i will be able to demonstrate creating a Bonus calculator for workers in our next tutorial.