Userforms for Data Entry
Automate Data Entry process using userforms
Suppose you need to enter data in an Excel file — student ID, course title and a grade each student received for a course. Entering all these records manually is tedious and time-consuming. A good alternative to this method is using VBA UserForms.
Let’s say there are 3 courses — Algebra, Geometry and Calculus. A student can get one of the following grades for each course:
- A, A-, B+, B, B-, C+, C, D, F
Let’s create a userform. As usual, insert a command button, right-click on it and select “View Code”. Insert a userform by selecting UserForm.
Right-click on UserForm1 and select “View Object”.
In the Toolbox window, select Label (letter A) and place 3 labels — Student ID, Course, Grade.
Select TextBox (ab|) and place it next to Student ID label.
For the other two labels, we’ll use combo boxes (first row, fourth column). Combo boxes are used when one of several suggested options needs to be selected. In this example, we have 3 courses and 9 possible grades. Need to choose one of all courses and a grade for this course, so use combo boxes.
Last, but not least. Add a couple of command buttons. We’re going to place two command buttons — Enter and Cancel.
That’s what UserForm1 looks like:
Now, to make the userform work for our purpose, right-click on the Enter button and select “View Code”.
Start by initializing the userform. Make sure that you have UserForm and Initialize selected in the drop-down menu.
Note: you can change names of labels and boxes in the Properties Window.
Private Sub UserForm_Initialize()
StudentId.Value=""
With Courses
.AddItem "Algebra"
.AddItem "Geometry"
.AddItem "Calculus"
End With
With Grades
.AddItem "A"
.AddItem "A-"
.AddItem "B+"
.AddItem "B+"
.AddItem "B-"
.AddItem "C+"
.AddItem "C"
.AddItem "D"
.AddItem "F"
End With
End Sub
The userform is initialized. Let’s make the Enter button do its job. Right-click on the button and select “View Code”. Next step, add the following lines between Private Sub CommandButton1_Click() and End Sub:
Private Sub CommandButton1_Click()
Dim emptyRow As Long
Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = StudentId.Value
Cells(emptyRow, 2).Value = Courses.Value
Cells(emptyRow, 3).Value = Grades.Value
End Sub
Notes:
- The COUNTA function is used for counting all cells containing some values, both numbers and letters.
- Range(“A:A”) is the entire column A.
Similarly, right-click on the Cancel Button and add the following line:
Private Sub CommandButton2_Click()
Unload Me
End Sub
Now, you can enter some data.