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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
