UserForm to Automate Calculations and Validate
Data
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.
Watch the video below to understand the details.