learn microsoft excel

UserForm to Automate Calculations and Validate Data

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Interactive Training

We'll contnue with our last userform and improve it to enter not only the name but also the salary and automate the calculations of the benefits and the total package.
Let's have a closer look at the new code for the command button of the improved user-form.

Private Sub cmdAddData_Click()
Dim benefits, total As Single
If Me.txtName.Value = "" Then
MsgBox "Please enter a name", vbExclamation, "Employee Data"
Me.txtName.SetFocus
End If
Range("A5") = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox "The Amount box must contain a number.", vbExclamation, "Employee Data"
Me.txtSalary.SetFocus
End If
Range("B5") = txtSalary.Value
benefits = txtSalary.Value * 0.5
Range("C5") = benefits
total = txtSalary.Value + benefits
Range("D5") = total
End Sub

  • The first and the last lines appear automatically when you double-click on the 'AddData' command button as you already know from the last video.
  • Next we define two variables called benefits and total which will have the data type 'Single'. This means that a decimal value will be assigned to these variables.
  • Next we write code to ensure that the user enters the name of the employee. In case the user doesn't do that he gets a message to please enter a name. When he clicks on 'OK' in the warning message the cursor is placed inside the name text box.
  • The entered name will be transferred to cell address A5.
  • The next lines of code ensure that the user enters the salary as a number. If he forgets to enter the salary amount or doesn't enter a number he gets a message to enter the data in number format. Data validation! When he clicks on 'OK' in the warning message the cursor is placed inside the salary text box.
  • Once the salary is entered the data is transferred to cell address B5.
  • Next the benefits and the total package are calculated as specified and the data is automatically transeferred to the active worksheet to cells C5 and D5 respectively.
In the next training video we'll add more features to the userform to make it even more user friendly.

Watch the video below to understand the details.



Older Video to Create a user form in Excel 2007 for Calculations. No sound.
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Interactive Training