consultantspaster.blogg.se

• #Vlookup in excel vba tutorial how to#

DataBodyRange(1, 3).Value = shForm.Range("G12").Value 'Gender DataBodyRange(1, 2).Value = shForm.Range("G10").Value 'Emp Name DataBodyRange(1, 1).Value = shForm.Range("G8").Value 'Updating Emp ID 'Referring ObjList and transferring data to DataBodyRange of Table Position:=1 'If row 1 is not blank in table then isert a new blank for at 1

Set shForm = ThisWorkbook.Sheets("Form") ' Assigning Form sheet to this variable Set objList = ThisWorkbook.Sheets("Database").ListObjects("EmpTable") ' Assigning Employee Table

Now move to Sheet1 (Form) code and write the below code to update Employee Name and Gender with lookup function.ĭim objList As ListObject ' ListObject to refer the Table created for Employeeĭim shForm As Worksheet 'Worksheet variable to refer Form Once you create all these sheets, press F+11 to open VBE window. To design this automated data entry form, just create these three different sheets with form, supporting data and Employee Table (EmpTable). Emp ID, Emp Name, Gender, Department, CTC, Submitted On and Submitted By.

In Database sheet, I have created a table name ‘EmpTable’ with headers e.g. User just needs to enter Emp ID in data entry form and form will automatically filled with Name and Gender.

Second sheet is supporting data to fetch some default data e.g. Let me explain the three different sheets used in this data entry application.įorm sheet is to enter the data and transfer it to database sheet in a Table.

#Vlookup in excel vba tutorial how to#

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. In this post, we will learn how to create Data Entry form and use Excel Table to store the data.