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.

UserForm1

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:

UserForm1

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:

1. The COUNTA function is used for counting all cells containing some values, both numbers and letters.
2. 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.