A data entry form saves time and keeps entries tidy. In this step-by-step tutorial, you’ll build an automated data entry form with a VBA UserForm. It saves each entry to a database sheet and shows the saved records in a list. You don’t need to know VBA already: you can copy the code below.

Here’s the finished form:

The finished automated data entry form

Set up the workbook

  1. Click File > Save As > Browse.

  2. Choose a folder, name the file Automated Data Entry Form, and set Save as type to Excel Macro-Enabled Workbook (*.xlsm). A normal .xlsx file can’t store macros.

    Saving as an Excel Macro-Enabled Workbook

  3. Click Save. The new name appears in the title bar.

    The new file name in the title bar

    You’ll need the Developer tab. If you don’t see it, turn it on: File > Options > Customize Ribbon, tick Developer, and click OK. See How to Show the Developer Tab.

  4. Rename the first sheet Home, and untick Gridlines on the View tab.

    The Home sheet with gridlines turned off

  5. Click Insert > Shapes, choose a rounded rectangle, and draw a button. Type a label on it, such as Launch Form. Then add a second sheet named Database.

    A rounded rectangle button on the Home sheet

  6. On Database, type these column headings in row 1: SL, Employee ID, Employee Name, Gender, Department, City, Country, Submitted By and Submitted On. Widen the columns, format the headings, add borders, and turn off gridlines.

    The Database sheet with column headings

Design the form

  1. On the Developer tab, click Visual Basic to open the VBA editor.

    The Visual Basic button on the Developer tab

  2. Click Insert > UserForm.

  3. In the Properties window, set Height to 325 and Width to 578. Set (Name) to frmForm (the code uses this name), and Caption to Automated Data Entry Form.

    The UserForm properties

  4. From the Toolbox, add a Frame. Set its caption to Enter Details, BorderStyle to 1, and BorderColor to Highlight.

  5. Add a second frame with the caption Database, styled as you like.

  6. Inside Enter Details, add 6 labels (copy and paste with Ctrl + C and Ctrl + V). Set their captions to match the headings: Emp ID, Emp Name, Gender, Department, City and Country.

  7. Add the input controls and give each a name in Properties, because the code refers to these names:

    ControlName
    Emp ID text boxtxtID
    Emp Name text boxtxtName
    Male option buttonoptMale
    Female option buttonoptFemale
    Department combo boxcmbDepartment
    City text boxtxtCity
    Country text boxtxtCountry
    Save buttoncmdSave (Accelerator: S)
    Reset buttoncmdReset (Accelerator: R)

    Then set each control’s TabIndex (1, 2, 3…) in the order users should move through them with Tab.

  8. In the Database frame, add a ListBox. Name it lstDatabase and set its TabIndex to 10 (Reset was 9).

  9. Press F5 to preview the form, then move and resize the controls until you’re happy with it.

    The designed form in preview

Add the code

  1. Click Insert > Module.

    Insert > Module in the VBA editor

  2. Paste this code into the module. It has three procedures: Reset clears the form and loads the saved records, Submit saves an entry, and Show_Form opens the form.

Sub Reset()
    Dim iRow As Long
    iRow = [Counta(Database!A:A)] ' last used row

    With frmForm
        .txtID.Value = ""
        .txtName.Value = ""
        .optMale.Value = False
        .optFemale.Value = False

        .cmbDepartment.Clear ' refill the department list
        .cmbDepartment.AddItem "Finance"
        .cmbDepartment.AddItem "Accounting"
        .cmbDepartment.AddItem "Marketing"
        .cmbDepartment.AddItem "Management"
        .cmbDepartment.AddItem "Production"

        .txtCity.Value = ""
        .txtCountry.Value = ""

        .lstDatabase.ColumnCount = 9 ' show the saved records
        .lstDatabase.ColumnHeads = True
        .lstDatabase.ColumnWidths = "30,60,75,40,60,45,55,70,70"
        If iRow > 1 Then
            .lstDatabase.RowSource = "Database!A2:I" & iRow
        Else
            .lstDatabase.RowSource = "Database!A2:I2"
        End If
    End With
End Sub

Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long
    Set sh = ThisWorkbook.Sheets("Database")
    iRow = [Counta(Database!A:A)] + 1 ' first empty row

    With sh
        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmForm.txtID.Value
        .Cells(iRow, 3) = frmForm.txtName.Value
        .Cells(iRow, 4) = IIf(frmForm.optFemale.Value = True, "Female", "Male")
        .Cells(iRow, 5) = frmForm.cmbDepartment.Value
        .Cells(iRow, 6) = frmForm.txtCity.Value
        .Cells(iRow, 7) = frmForm.txtCountry.Value
        .Cells(iRow, 8) = Application.UserName
        .Cells(iRow, 9) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
    End With
End Sub

Sub Show_Form()
    frmForm.Show
End Sub
  1. Now connect the buttons. In the Project panel, open frmForm and double-click the Save button to open the form’s code.

    Opening the form's code from the Save button

  2. Replace what’s there with this code:

Private Sub cmdReset_Click()
    Dim msgValue As VbMsgBoxResult
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
    If msgValue = vbNo Then Exit Sub
    Call Reset
End Sub

Private Sub cmdSave_Click()
    Dim msgValue As VbMsgBoxResult
    msgValue = MsgBox("Do you want to save this entry?", vbYesNo + vbInformation, "Confirmation")
    If msgValue = vbNo Then Exit Sub
    Call Submit
    Call Reset
End Sub

Private Sub UserForm_Initialize()
    Call Reset
End Sub
  1. The code window should look like this:

    The form's code in the VBA editor

  2. Save, then click Debug > Compile VBAProject to check for errors.

    Debug > Compile VBAProject

  3. Click the Excel icon to go back to the workbook.

    Returning to Excel from the VBA editor

Connect the launch button

  1. On the Home sheet, right-click your button and click Assign Macro.

    Assign Macro in the button's right-click menu

  2. Choose Show_Form and click OK.

    Choosing the Show_Form macro

  3. Click the button and add a few entries. Each one is saved to the Database sheet and appears in the list on the form.

    The form with saved entries in the list

Tip: To stop empty or incomplete entries, add a check at the start of cmdSave_Click. For example: If frmForm.txtID.Value = "" Then MsgBox "Please enter an Emp ID.": Exit Sub.

Note: When you open a macro workbook, Excel may show a security warning. Click Enable Content to run the macros. If Windows blocks macros in a downloaded file, right-click the file, click Properties, tick Unblock, and click OK.

Download the finished workbook: excel-data-entry-form.xlsm

New to Excel? Start with the free Excel Essentials course.