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:

Set up the workbook
-
Click File > Save As > Browse.
-
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.

-
Click Save. The new name appears 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.
-
Rename the first sheet Home, and untick Gridlines on the View tab.

-
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.

-
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.

Design the form
-
On the Developer tab, click Visual Basic to open the VBA editor.

-
Click Insert > UserForm.
-
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.

-
From the Toolbox, add a Frame. Set its caption to Enter Details, BorderStyle to 1, and BorderColor to Highlight.
-
Add a second frame with the caption Database, styled as you like.
-
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.
-
Add the input controls and give each a name in Properties, because the code refers to these names:
Control Name Emp ID text box txtIDEmp Name text box txtNameMale option button optMaleFemale option button optFemaleDepartment combo box cmbDepartmentCity text box txtCityCountry text box txtCountrySave button cmdSave(Accelerator: S)Reset button cmdReset(Accelerator: R)Then set each control’s TabIndex (1, 2, 3…) in the order users should move through them with Tab.
-
In the Database frame, add a ListBox. Name it
lstDatabaseand set its TabIndex to 10 (Reset was 9). -
Press F5 to preview the form, then move and resize the controls until you’re happy with it.

Add the code
-
Click Insert > Module.

-
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
-
Now connect the buttons. In the Project panel, open frmForm and double-click the Save button to open the form’s code.

-
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
-
The code window should look like this:

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

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

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

-
Choose Show_Form and click OK.

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

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.