User Form to Automate Calculations in Excel

We’ll continue 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”
End If
Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
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 transferred 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.

3 thoughts on “User Form to Automate Calculations in Excel

  1. devika

    Private Sub CommandButton1_Click()
    Dim transfer As Long

    transfer = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
    Sheets(“sheet1”).Cells(transfer + 1, “B”).Value = TextBox1.Text
    Sheets(“sheet1”).Cells(transfer + 1, “C”).Value = TextBox2.Text
    Sheets(“sheet1”).Cells(transfer + 1, “D”).Value = TextBox3.Text
    Sheets(“sheet1”).Cells(transfer + 1, “E”).Value = TextBox4.Text
    Sheets(“sheet1”).Cells(transfer + 1, “F”).Value = TextBox8.Text
    Sheets(“sheet1”).Cells(transfer + 1, “G”).Value = TextBox5.Text
    Sheets(“sheet1”).Cells(transfer + 1, “H”).Value = TextBox7.Text
    Sheets(“sheet1”).Cells(transfer + 1, “I”).Value = ComboBox1.Value

    end Sub

    i am trying to transfer data from a survey form to excel using the ablove code and i get”compile error”

    1. Dinesh Kumar Takyar Post author

      Tested. Works fine. It just doesn’t enter the data in the next row because your definition of transfer takes only “A” into account and you are not entering any data into “A”! View more solutions at


Leave a Reply

Your email address will not be published. Required fields are marked *