One of the main advantages of MS Excel is its ability to work with homogeneous data arrays, called lists.
Lists in MS Excel are tables where rows contain uniform information.
The rows of the table are called records, and the columns are called fields of the records.
Column fields are assigned unique names, which are placed in the first row of the list—the header row.
In Microsoft Office Excel 2010, for example, the following methods can be used to enter data into a list:
- Using the data form, which is automatically created after defining the list header with the Form command.
NOTE
In earlier versions of MS Excel (including 2003), the data form could be called using the menu command Data | Form.
In MS Excel 2007 and MS Excel 2010, you must add the corresponding Form command to the Quick Access Toolbar or to a ribbon tab by using the Excel Options window and selecting either Customize Ribbon or Quick Access Toolbar.
- Entering data directly into empty rows inserted into the list (in this case, the list range name is automatically redefined).
- Using AutoFill, Series, and the Pick from List command to speed up work.
- Using MS Access forms and then transferring the data into an Excel worksheet.
- Using VBA—where a custom program you write provides a form or dialog box for data entry, which then inserts the values into specified worksheet cells.
The built-in Form tool, whose dialog box is displayed by choosing the Form command, allows filling and editing records in a table list.
A major drawback of this tool is that each field of a record corresponds only to a simple text box.
However, when filling list data, values for some fields are often chosen from a limited set of alternatives (e.g., gender can be male or female, the list of employees in a sales department is limited, etc.).
For such fields, it is more convenient to use list boxes, option buttons, and checkboxes instead of plain text fields.
This approach both speeds up the data entry process and reduces errors (typos) that inevitably occur during manual entry.
Example application
Let us create an application that eliminates the drawbacks of the standard form.
Suppose you are a manager of a travel agency called Through Space and Time! and you are recording information about each client in a data list.
To speed up data entry, you decide to create an application with a dialog box.
To avoid mistakes, repetitive information such as tour destination and type of transport will be selected from lists.
These lists will be filled based on data stored in the worksheets Tour and Transport.
Constructing the application
Create a form and place on it:
- three text boxes,
- five labels,
- a spin button,
- two buttons,
- two combo boxes,
- one frame with two option buttons,
- and another frame with three checkboxes.
Using the Properties window, assign the properties shown in Table.
Table. Property values set in the Properties window
| Object | Property | Value |
| Form | Caption | Registration of tourists of “Through Space and Time!” |
| Label | Caption | Last Name |
| TextBox | Name | txtLName |
| Label | Caption | First Name |
| TextBox | Name | txtFName |
| Frame | Caption | Payment and Documents |
| CheckBox | Caption | Paid |
| CheckBox | Caption | Photo |
| CheckBox | Caption | Passport |
| Label | Caption | Duration (days) |
| TextBox | Name | txtDays |
| SpinButton | Name | SpnDays |
| Frame | Caption | Gender |
| OptionButton | Name | optMale |
| OptionButton | Name | optFemale |
| Label | Caption | Destination (Tour) |
| ComboBox | Name | cmbTour |
| Label | Caption | Transport Type |
| ComboBox | Name | cmbTrans |
| Button | Name | cmdOK |
| Button | Name | cmdCancel |
Form Module Functionality
In the form module, enter the necessary code, which provides:
- Reading data from the dialog box and entering the record into the first empty row of the data table.
- Entering the duration of the tour either via the keyboard or the spin button, which work synchronously.
- Protecting all data on the worksheets from being modified by the user.