Displaying a Data Form

Many people use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a built-in data entry form that Excel can create automatically. This data form works with either a normal range of data or a range that has been designated as a table (by using the Insert Tables Table command). Figure 12-13 shows an example of a data form in use.

Figure 12-13: Some users prefer to use Excel's built-in data form for data-entry tasks .

Making the data form accessible

For some reason, the command to access the data form is not in the Excel 2007 Ribbon. In order to access the data form from Excel's user interface, you must add it to your Quick Access Toolbar (QAT):

1. Right-click the QAT and select Customize Quick Access Toolbar. This displays the Customization panel of the Excel Options dialog box.

2. In the Choose Commands From drop-down, select Commands Not in the Ribbon.

3. In the list box on the left, select Form.

4. Click the Add button to add the selected command to your QAT.

5. Click OK to close the Excel Options dialog box.

   After performing these steps, a new icon will appear on your QAT.

To use a data entry form, you must arrange your data so that Excel can recognize it as a table. Start by entering headings for the columns in the first row of your data entry range. Select any cell in the table and click the Form button on your QAT. Excel then displays a dialog box customized to your data. You can use the Tab key to move between the text boxes and supply information. If a cell contains a formula, the formula result appears as text (not as an edit box). In other words, you cannot modify formulas from the data entry form.

When you complete the data form, click the New button. Excel enters the data into a row in the worksheet and clears the dialog box for the next row of data.

Displaying a data form by using VBA

Use the ShowDataForm method to display Excel's data form. The only requirement is that the active cell must be within a range. The following code activates cell A1 (which is in a table) and then displays the data form:

 Sub DisplayDataForm()     Range("A1").Select     ActiveSheet.ShowDataForm End Sub 

CD-ROM  

A workbook with this example is available on the companion CD-ROM. The file is named  data form example.xlsm .