Data Entry Form with Excel Table and Vlookup

In this post, we will learn how to create Data Entry form and use Excel Table to store the data. We will also learn how to use in Lookup Functions in Form to fetch some default or base data to reduce the data entry task.

Let me explain the three different sheets used in this data entry application.

Form sheet is to enter the data and transfer it to database sheet in a Table.

Second sheet is supporting data to fetch some default data e.g. Name and Gender.  User just needs to enter Emp ID in data entry form and form will automatically filled with Name and Gender.

The third sheet is Database. In Database sheet, I have created a table name ‘EmpTable’ with headers e.g. Emp ID, Emp Name, Gender, Department, CTC, Submitted On and Submitted By.

To design this automated data entry form, just create these three different sheets with form, supporting data and Employee Table (EmpTable).

Once you create all these sheets, press F+11 to open VBE window.

Insert a blank module in this project.

Now move to Sheet1 (Form) code and write the below code to update Employee Name and Gender with lookup function.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 8 And Target.Column = 7 Then
        Application.EnableEvents = False 'Disable the Events so that Worksheet_Change event will not get fired while updating the value to other cells
        Sheets("Form").Range("G10").Value = [IFERROR(VLOOKUP($G$8,'Supporting Data'!$A$1:$C$9,2,0),"")]
        Sheets("Form").Range("G12").Value = [IFERROR(VLOOKUP($G$8,'Supporting Data'!$A$1:$C$9,3,0),"")]
        
    End If
    
    Application.EnableEvents = True 'Enabling the Excel events
    
End Sub

Now, move to Module1 and write the below Sub Procedure to Transfer the data to EmpTable.

Sub Transfer()
        
    Dim objList As ListObject ' ListObject to refer the Table created for Employee
    Dim shForm As Worksheet 'Worksheet variable to refer Form
    
    Set objList = ThisWorkbook.Sheets("Database").ListObjects("EmpTable") ' Assigning Employee Table
    
    Set shForm = ThisWorkbook.Sheets("Form") ' Assigning Form sheet to this variable
    
    If objList.DataBodyRange(1, 1).Value <> "" Then ' Identifying whether row 1 is blank in current table or not
    
       objList.ListRows.add Position:=1 'If row 1 is not blank in table then isert a new blank for at 1
        
    End If
    
    
    'Referring ObjList and transferring data to DataBodyRange of Table
    
    With objList
    
      .DataBodyRange(1, 1).Value = shForm.Range("G8").Value 'Updating Emp ID
      .DataBodyRange(1, 2).Value = shForm.Range("G10").Value 'Emp Name
      .DataBodyRange(1, 3).Value = shForm.Range("G12").Value 'Gender
      .DataBodyRange(1, 4).Value = shForm.Range("G14").Value 'Department
      .DataBodyRange(1, 5).Value = shForm.Range("G16").Value 'CTC
            
      .DataBodyRange(1, 6).Value = Format([Now()], "DD-MM-YYYY HH:MM:SS") 'Submitted On
      .DataBodyRange(1, 7).Value = Application.UserName 'Submitted By
    
    End With
    
    'Reset the fields in Form sheet
    
    shForm.Range("G8,G10,G12,G14,G16").Value = ""
        
    ThisWorkbook.Save 'Save this workbook
 
End Sub

Now we have done with Trasfer Sub Procedure. Let us create a Sub Procedure so that it can be assigned on Transfer button in Data Entry Form.

Sub TransferToTable()

    Dim iMessage As VbMsgBoxResult
    
    iMessage = MsgBox("Do you want to transfer the data?", vbYesNo + vbQuestion, "Confirmation")
    
    If iMessage = vbNo Then Exit Sub
    
        Call Transfer
        
End Sub

Let us create another Sub Procedure to Reset the Form.

Sub Reset()
   Dim iMessage As VbMsgBoxResult

   iMessage = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Confirmation")

   If iMessage = vbNo Then Exit Sub

    ThisWorkbook.Sheets("Form").Range("G8,G10,G12,G14,G16").Value = ""
End Sub

Now we have done with all the required codes. Let us move to Excel window and assign the Macros on Transfer and Reset button.

On Transfer button, assign the macro ‘TransferToTable’ and on Reset button, assign the ‘Reset’ procedure.

Please watch the complete tutorial on YouTube.

Click on below button to download the Excel file used in this tutorial.

Please visit to our other data entry tutorial pages.