An Enhanced Data Form

Next is one of the more complex UserForms that you'll encounter. I designed it as a replacement for Excel's built-in Data Form, which is shown in Figure 15-26.

Figure 15-26: Excel's Data Form.

Note  

Displaying Excel's Data Form is not easy in Excel 2007. You need to use the Excel Options dialog box, click the Customization tab, and add the Form command from the Commands Not in the Ribbon group . Then, the Form command will appear on your Quick Access Toolbar.

Like Excel's Data Form, my Enhanced Data Form works with a list in a worksheet. But as you can see in Figure 15-27, it has a dramatically different appearance and offers several advantages.

Figure 15-27: My Enhanced Data Form.

About the Enhanced Data Form

The Enhanced Data Form features the enhancements listed in Table 15-1.

Table 15-1: COMPARING THE ENHANCED DATA FORM WITH THE EXCEL DATA FORM
Open table as spreadsheet

Enhanced Data Form	Excel Data Form
Handles any number of records and fields.	Limited to 32 fields.
Dialog box can be displayed in any size that you like.	Dialog box adjusts its size based on the number of fields. In fact, it can take up the entire screen!
Fields can consist of either InputBox or ComboBox controls.	Uses only InputBoxes.
Record displayed in the dialog box is always visible onscreen and is highlighted so you know exactly where you are.	Doesn't scroll the screen for you and doesn't highlight the current record.
At start-up, the dialog box always displays the record at the active cell .	Always starts with the first record in the database.
When you close the dialog box, the current record is selected for you.	Doesn't change your selection when you exit.
Lets you insert a new record at any position in the database.	Adds new records only at the end of the database.
Includes an Undo button for Data Entry, Insert Record, Delete Record, and New Record.	Includes only a Restore button.
Search criteria are stored in a separate panel, so you always know exactly what you're searching for.	The search criteria are not always apparent.
Supports approximate matches while searching (*, ?, and #).	Excel's Data Form does not support wildcard characters .
The complete VBA source code is available, so you can customize it to your needs.	Data Form is not written in VBA and cannot be customized.

CD-ROM  

The Enhanced Data Form is a commercial product ( sort of). The Excel 2003 version of the add-in is available on the companion CD-ROM, and it can be used and distributed freely .

To download a copy of the Excel 2007 version, visit my Web site: http://www.j-walk.com/ss. If you would like to customize the code or UserForm, access to the complete VBA source is available for a modest fee.

Installing the Enhanced Data Form add-in

To try out the Enhanced Data Form, install the add-in:

1. Copy the  dataform2.xla file from the CD-ROM to a directory on your hard drive.

2. In Excel, choose Office Excel Options.

3. In the Excel Options dialog box, click the Add-Ins tab.

4. Select Excel Add-Ins from the Manage drop-down list and click Go to display the Add-Ins dialog box.

5. In the Add-Ins dialog box, click Browse and locate the  dataform2.xla in the directory from Step 1.

After performing these steps, you can access the Enhanced Data Form by using the Add-Ins Menu Commands JWalk Enhanced Data Form command. You can use the Enhanced Data Form to work with any worksheet list or table.

Note  

Version 3 of this product will be incorporated into the Ribbon and will also feature a resizable UserForm and a few other new features. The updated version was not ready in time to be included on the CD-ROM.