Userforms with calculations

Userforms, as we know, provide an intituitive way to enter data into an Excel worksheet. We can also code the command buttons to automate the process of calculations and then transfer the calculated as well as the other data into an Excel worksheet. The process again involves defining the variables that will detect an empty row, copy the data from the userform to the worksheet and finally clear the data from the user form so that we can input fresh data again. In the example shown in the training video, we input the name and salary of the employee. On clicking on the add button, the House Rent Allowance, Provident Fund and Medical expenses are automatically calculated and then the Package that an employee receives every month is also computed. This calculated data and the name and salary are then automatically transferred to the Excel worksheet under the proper headings.

In this video you will also note that the TextBoxes in the user form have been appropriately named. For example, the TextBox with the label Name has been named txtName.

The code for the 'AddData' command button is also given here for your ready reference:

Dim eRow As Long
Dim ws As Worksheet
Set ws = Worksheets("data")

'find first empty row in database
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a name
If Trim(Me.txtName.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a name"
Exit Sub
End If

'copy the data to the database
ws.Cells(eRow, 1).Value = Me.txtName.Value
ws.Cells(eRow, 2).Value = Me.txtSalary.Value
ws.Cells(eRow, 3).Value = Me.txtHRA.Value
ws.Cells(eRow, 4).Value = Me.txtPF.Value
ws.Cells(eRow,5).Value=Me.txtMed.Value
ws.Cells(eRow,6).Value=Me.txtPkg.Value

'clear the data
Me.txtName.Value = ""
Me.txtSalary.Value = ""
Me.txtHRA.Value = ""
Me.txtPF.Value = ""
Me.txtMed.Value=""
Me.txtPkg.Value = ""
Me.txtName.SetFocus