The major drawback of the last userform was that the data could be entered only in specific cells and the data would be overwritten. Now using an appropriate code in the userform command button we can find the next empty row for automatic data entry in our Excel worksheet.
Let’s have a closer look at the new code for the command button of the improved user-form. Here we have assumed that we have header labels starting at cell address A4.
Private Sub cmdAddData_Click()
Dim RowCount As Long
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
RowCount = Worksheets(“Sheet1”).Range(“A4”).CurrentRegion.Rows.Count
.Offset(RowCount, 0) = Me.txtName.Value
.Offset(RowCount, 1) = Me.txtSalary.Value
.Offset(RowCount, 2) = benefits
.Offset(RowCount, 3) = total
- The first line of code represents a new variable called RowCount. Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.
- RowCount = Worksheets(“Sheet1”).Range(“A4”).CurrentRegion.Rows.Count: This statement counts how many rows of data are included in the region that includes cell A4 and stores that number in the RowCount variable.
- Now using the Offset property which requires two numbers, the first representing the number of rows (RowCount) away from cell A4 , the second representing the number of columns away from cell A4, we can access the appropriate cells in the Excel worksheet to automatically enter the name, salary, benefits and the total package of the employee .
In the next training video we’ll refine the concepts of the userorm further.
Watch the Excel VBA training video below to understand the details.