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”
Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
Range(“B5”) = txtSalary.Value
benefits = txtSalary.Value * 0.5
Range(“C5”) = benefits
total = txtSalary.Value + benefits
Range(“D5”) = total
- 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.