Excel Data Entry Form

Here is a simple way to make your Excel worksheet as a simple database. In our example, we will create a vendors table and we will use an Excel data entry form to allow user to populate the vendors table. Here's how to do it...

Step 1: Create a new workbook. Rename the first worksheet as "Vendor". It will become the repository of the vendor information that the users will enter to our data entry form.

Step 2: At the first row, enter the following "column" names...

• Vendor ID
• Vendor Name
• Address 1
• Address 2
• City
• Country
• Zip
• Phone 1
• Phone 2
• Fax
• Contact

Here's how the worksheet should look when you enter them:


These "column" names are just example of what you can put in a vendor table. You can add your own columns or delete some that you think you don't need in your own application.

Step 3: Select all the cells of the column names.

Step 4: At the menu, click Data>Form as seen in the screen shot below:

You will be prompted with multiple questions; but your answer should be to make the first row of the highlighted cells as the columns (or labels). This question is only when the excel database has no records in it. The next time, when there are already records in it, this will not appear. The "Ok" button should be pressed at this point.

Step 5: The Excel data entry form is now in front of you. Here's how it looks...


Step 6: Like a usual data entry form, there are things that you can do with the form. To enter a new record, enter the information in each field and click the "New" button. "Delete" button is used to delete a record. "Restore" button is used to restore previous content of the fields when you amend the fields. "Find Prev", "Find Next" and "Criteria" are for records navigation. I have to say that most of the time the "Criteria" is not functioning well or maybe I was doing something wrong to make it act unusual.

So that's how to go through the Excel data entry form. This small database management is should be enough for small needs, but as the needs grow new methods are should be used instead of this. In the upcoming posts in the near future, I will discuss other ways to do that. not all in a single post, but in a subsequent series of posts.

You better subscribe now. See below how...

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.