Generic forms

Excel has, within the VBA Editor, a form design tool that helps you easily fill in structured data tables. However, some of you still do not know the VBA and are unable to create the form yourself. Some others will find themselves in the situation that their job is overwhelmed by entering data one time. For all of you, in the “recipe” that follows, I will show how to enter data in a table using generic Excel forms.

For start, let’s open a new Excel document. Choose a template called Simple Invoice. This will be done because the template contains a table, and it is needed to demonstrate work with generic forms.

Then, we need to enable the use of the form. We will do this by adding the form icon to one of the ribbons or, as in this example, in the Quick Access Toolbar. Right-click the File tab, and then select the Customize Quick Access Toolbar option. In the Choose Commands from drop-down list, select All Commands, and then find the Form option and, by clicking the Add button, add it to the right-hand panel where all the options in the quick toolbar are presented. We are finishing setup and we are ready to work!

If you are positioning on a table, then clicking the Forms icon will open a generic form that allows you to add new records, browse records, etc.

Immediately after the form starts, it is in the input mode. By clicking the New button we add a new record, and by clicking Delete, we delete the record that we are currently in. If you want to run the search mode, you need to click on the Criteria button. In this case we do not enter the data in the table, but the entered fields serve as a search criterion. We return to the entry mode by clicking on the Form button. You can enter or search as much as you like, and when you want to finish the entry you do so by clicking the Close button.