VBA Userform BMI and Age Calculator
Excel VBA Calculations in a Userform
In this tutorial I’ll be showing you how you can create a BMI and Age Calculator in a userform. All of the calculations will be carried out on the worksheet and then returned to the userform.
Other Userform Calculation Tutorials
Download the Template for BMI and Age Calculator
I would recommend that you download the template because it will make it easier for you to complete this tutorial. The references below are designed for this template. So save time and heartache – use the template.
Video: BMI and Age Calculator
This video is designed to help you understand how the application will work. It will also give you step-by-step instructions on creating the Userform and where you should add the code that you will find below.
BMI and Age Calculator Process
Adding the Worksheet formulas
Note: When you add these formulas to the worksheet click on the cell but make sure that you paste the formula into the formula bar at the top, 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
Creating the Userform
Make sure that you have watched the video and create the Userform as shown in the illustration above. Notice the names for the textboxes and command buttons.
You will need to use these exact names for the code that you will be later adding to the Userform to work.
This Userform thus consists of
5 text boxes
2 command buttons
Properties for the Userform
Note: There are a couple of things that you can do with the Userform that will make it user friendly.
1. Name it appropriately (does not matter what name you choose but something like frmAge would be suitable.
2. Add a caption to describe what action this Userform will be achieving. Such as “Age and BMI Calculator”
Adding the VBA code to the Userform
Here is the code for the Userform. Double click inside the Userform and then copy the code below and paste into the Visual Basic editor. If you have not already done so make sure you watch the video to get a brief understanding of what this code is doing.
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
MsgBox "You need to add a proper date"
txtDOB.Value = ""
'Check for Height value and add value
If IsNumeric(txtHeight) And txtHeight <> 0 Then
Sheet1.Range("F11") = Format(Me.txtHeight.Value, "###0.0")
MsgBox "You must add a Height"
'Check for Weight value and add value
If IsNumeric(txtWeight) And txtWeight <> 0 Then
Sheet1.Range("G11") = Format(Me.txtWeight.Value, "###0.0")
MsgBox "You must add a Weight"
'Show the BMI result
Me.txtBMI = BMI
Me.txtBMI.Value = Format(BMI, "#,##0.0")
Private Sub cmdClose_Click()
Run the Userform
In the Assorted module to your VBA editor add the code below.
Right Click the shape on the worksheet and then choose Assign Macro and assign the macro below. This will call the userform.
I hope you have enjoyed this tutorial to show how you can do calculations in a Userform quite simply. I will demonstrate creating a Bonus calculator for employees in our next tutorial.