J-Walk & Associates, Inc.

Purchase the VBA Password

The Enhanced Data Form is a free add-in, written entirely in VBA. The VBA project is protected, but the password is available for a small fee. Accessing the VBA source will allow you to customize the add-in. Or, you may want to view the code to learn how it works. Many users have reported that studying this code is an excellent way to learn some useful programming techniques.

• Click here to order the password online

Details

• The price is U.S. $15.00. 
• You are ordering the password only. This assumes that you have already downloaded the Enhanced Data Form v3 add-in.
• When you order the password, you will download a zip file that contains a PDF file with the password and additional information that describes how the program works.
• The password is the same for the v3a (Excel 97-2003) and v3b (Excel 2007).
• It is important to understand that you must be familiar with VBA in order to make use of the code in this add-in.

Upgrading?

If you purchased the password for a previous version of the J-Walk Enhanced Data Form, that password will not work with v3.

Remember, the product itself is free. Access to the VBA source code requires a small fee, and discounted upgrades are not available for this product. However, the version 3 password fee has been reduced by $5.

Technical Support

• Limited technical support is available via email at no charge.

• Please note that I am not able to provide free programming assistance for those who want to customize the product and have purchased the VBA password.

Download and Install

The J-Walk Enhanced Data Form v3 is a standard Excel add-in file. It does not use any custom DLLs, and it makes no changes to your system. In other words, it was designed to be as generic and problem-free as possible.

• Note: You will be downloading a compressed ZIP file. This file contains both sub-versions of the Enhanced Data Form (dataform3.xla and dataform3.xlam). If you use Excel 2007, make sure that you install the *.xlam add-in. Otherwise, install the *.xla add-in.

For Excel 97 - 2003:

To install the add-in:

1. Download the J-Walk Enhanced Data Form add-in. 
2. Double-click the ZIP file and extract the dataform3.xla file. This file can be stored anywhere on your system.
3. Start (or activate) Excel.
4. Choose Tools - Add-Ins to display the Add-Ins dialog box.
5. In the Add-Ins dialog box, click the Browse button.
6. Locate the dataform3.xla file (the file you extracted in Step #2)

After performing these steps, the Data menu displays a new menu item: J-Walk Enhanced Data Form. The add-in will be available for all future Excel sessions.

• Note: You can also use Excel's Open dialog box to open the dataform3.xla file. However, this method does not install the add-in, so it will not be available automatically the next time your start Excel.

For Excel 2007:

To install the add-in:

1. Download the J-Walk Enhanced Data Form v3b add-in. 
2. Double-click the ZIP file and extract the dataform3.xlam file. This file can be stored anywhere on your system.
3. Start (or activate) Excel 2007 or later.
4. Press Alt+TI to display the Add-Ins dialog box.
5. In the Add-Ins dialog box, click the Browse button.
6. Locate the dataform3.xlam file (the file you extracted in Step #2)

After performing these steps, the Data tab on Excel's ribbon will display a new group, with a single icon:  J-Walk Enhanced Data Form. The add-in will be available for all future Excel sessions.

• Note: You can also use Excel's Open dialog box to open the dataform3.xlam file. However, this method does not install the add-in, so it will not be available automatically the next time your start Excel.

Uninstall

To uninstall the add-in:

1. Press Alt+TI to display Excel's Add-Ins dialog box.
2. Remove the checkmark from the 'Enhanced Data Form v3' item.

After performing this step, the Enhanced Data Form add-in will not be loaded when Excel starts.

Frequently Asked Questions

This document may answer some questions you have about about the J-Walk Enhanced Data Form.

Why are there two sub-versions of the Enhanced Data Form?

Excel 2007 has a new Ribbon-based user interface and uses a new file format. The Version 3b sub-version (dataform3.xlam) uses this new file format, and modifies the interface so you can access the Enhanced Data Form from the Ribbon.

Note: The Version 3a sub-version will work with Excel 2007, but the Ribbon won't be modified when the add-in is installed. Rather, the EDF icon will be displayed in a tab named Add-Ins.

Why doesn't the Enhanced Data Form does show my field names correctly?

If your field names are lengthy, they may appear cut off in the Form. This is normal. The default width of the field name is 60 units. You can change this value by creating a new name in the workbook. See Customizing the Data Form.

Why doesn't the Enhanced Data Form recognize my database correctly?

The worksheet database is identified based on the position of the active cell. It uses the "Current Region" around the active cell. To determine what the current region is, activate a cell and press F5 to display the Go To dialog box. Click the Special button, choose the Current region option, and click OK. Excel will select the current region -- which is the database range used by the Enhanced Data Form.

The database range may not be what you expect it to be because of the following:

• Your field headings consist of more than one row
• You have one or more blank rows within your database
• You have one or more blank columns within your database

Excel's Data Form recognizes a range named "Database" but the Enhanced Data Form does not. Is this a bug?

No, this is by design.

Why are some of the fields "grayed out" in the Enhanced Data Form?

If a cell contains a formula, the field is grayed out so you won't overwrite the formula with a value.

When I insert a new record, why does the word "[New]" appear in the first field?

That text appears so the record won't be empty. An empty record would break up the database -- something that you probably don't want to happen. Just start typing the entry for the first field to overwrite the "[New]" text.

When I click the Insert button to insert a new record, the fields that contain formulas are updated. But sometimes the formulas are wrong!

When a new field is inserted, the Enhanced Data Form examines the current row. If any fields contain formulas, those formulas are copied to the inserted row. If the formulas refer to cells in a different row, the formula will probably not be correct.

For example, assume cell C10 contains this formula: =C9+1. If the current record is row 10 and you insert a new row, row 10 is shifted down and the formula (now in C11) will continue to refer to C9. It will not refer to the newly inserted row. In such a case, you will need to edit your data so the formulas are correct.

To avoid this type of problem, use the New button to add the record to the end of the database.

My data is formatted to display a currency sign, but the symbol does not appear in the Enhanced Data Form.

This is by design. The Enhanced Data Form displays the value as it appears in the Formula bar. If you change the data, it will be written back to the worksheet using the original formatting.

Is it possible to make the Enhanced Data form wider or taller?

Yes, you can do so manually by clicking the lower right corner and dragging. To force the Enhanced Data Form to display at a particular size, you need to define one or two names. See Customizing the Data Form for more information.

I purchased the password for Enhanced Data Form v2, but it does not work with v3. Why not?

Enhanced Data Form v3 uses a different VBA password. If you wish to view or customize the code, you'll need to purchase the new password. Remember, this product is free and has always been free. Access to the VBA code requires a small fee (US $15).

Customizing the Enhanced Data Form

This document describes how to customize the J-Walk Enhanced Data Form. These customizations are optional.

• Changing the Enhanced Data Form size
• Changing the width of the field names
• Making a field display as a combo box
• Changing the language used in the Enhanced Data Form (password required)
• Displaying the Enhanced Data Form using a macro
• Using the Enter key to move among the fields (password required)

Changing the Enhanced Data Form size

By default, the Enhanced Data form is 270 pixels wide, and 240 pixels high. You can easily change the size of the dialog box by creating one or two names in your workbook.

The instructions that follow assume that you're using Excel 2007.

To change the width of the Enhanced Data Form:

1. Activate the workbook that contains a database.
2. Select Formulas - Defined Names - Define Name to display the New Name dialog box. (For previous versions, use Insert - Name -Define to display the Define Name dialog box)
3. In the Name field, enter DF_WIDTH (uppercase or lowercase). (In previous versions, the field is Names in workbook)
4. In the Refers to field, enter a number that corresponds to the desired width. The number must be greater than 270. Or, enter a cell reference that contains a value for the width.
5. Click OK to add the name to the workbook.

To change the height of the Enhanced Data Form:

1. Activate the workbook that contains a database.
2. Select Formulas - Defined Names - Define Name to display the New Name dialog box. (For previous versions, use Insert - Name -Define to display the Define Name dialog box)
3. In the Name  field, enter DF_HEIGHT (uppercase or lowercase). (In previous versions, the field is Names in workbook)
4. In the Refers to field, enter a number that corresponds to the desired width. The number must be greater than 240. Or, enter a cell reference that contains a value for the height.
5. Click OK to add the name to the workbook.

The figure below shows the Excel 2007 Name Manger dialog box after adding two names.



After adding these names, the Enhanced Data Form will be displayed using the new dimensions.  In this case, the Enhanced Data Form is 340 pixels wide and 320 pixels high. Note that you can create one name or both names. In some cases, a bit of trial and error may be necessary to optimize the dialog box dimensions for a particular database table.

• Note: A new feature enables the user to change the size of the Enhanced Data Form dynamically. Just click the lower right corner and drag. If, for some reason, you don't want the user to be able to change the dialog box name, create a new name DF_NORESIZE. If this name evaluates to FALSE, the user will not be able to resize the form dynamically. If this name contains TRUE (or doesn't exist), then the form is resizable.

Changing the width of the field names

By default, the field names displayed in the Enhanced Data Form are 60 units wide. If your database table contains longer text strings, you may want to increase the size of the field names. Or, if your field names contain short strings, you may want to decrease the width of the field names to provide more space for the data entry controls.

To change the width of the field names displayed in the Enhanced Data Form, follow the instructions below. These instructions are for Excel 2007.

1. Activate the workbook that contains a database.
2. Select Formulas - Defined Names - Define Name to display the New Name dialog box. (For previous versions, use Insert - Name -Define to display the Define Name dialog box)
3. In the Name  field, enter DF_FIELDWIDTH (uppercase or lowercase). (In previous versions, the field is Names in workbook)
4. In the Refers to field, enter a number that corresponds to the desired width. Or, enter a cell reference that contains a value for the height.
5. Click OK to add the name to the workbook.

In some cases, a bit of trial and error may be necessary to fine-tune the field name width for a particular database table.

• Note: When the Enhanced Data Form is resized dynamically, the width of the field names does not change. Rather, the width of the data entry controls are adjusted.

Making a field display as a combo box

By default, each field in the Enhanced Data Form displays its data in a Text Box. In some cases, you may prefer to use a drop-down list (a Combo Box) that provides a list of options. This is done by entering a list of the items into a range, and then creating a name for that range. The name must correspond to the field name.



For example, assume that your database has a field named Region, and you would like the Enhanced Data Form to display a list of the four regions: North, South, East, and West. Follow these steps (which assume that you're using Excel 2007):

1. Activate your workbook that contains a database.
2. Enter the four region names into a range. This range can be on the worksheet that contains the database, or in any other worksheet in the workbook. And, the sheet can be hidden.
3. Select the range.
4. Choose Formulas - Defined Names - Define Name to display the New Name dialog box. (For previous versions, use Insert - Name -Define to display the Define Name dialog box)
5. In the Name field, enter Region. This name must correspond to a field name in the first row of your database. (In previous versions, the field is Names in workbook)
6. The Refers to field will contain the address of the range you selected in Step 3.
7. Click OK to add the name to the workbook.

After defining this name, the Enhanced Data Form will display a drop-down list for the Region field.

Tips and Notes:

• Displaying a Combo Box for a field is a convenience feature. It will not prevent other information (not in the list) from being entered for that field.
• If the field name includes one or more spaces, substitute an underscore character for each space in the name. For example if the field is named "Tax Code" define a name "Tax_Code".
• The range can be defined as an entire row or column, which includes blank cells. In fact, a column in your database can be defined as a name. In such a case, the Combo Box will list all items currently in that field.
• If the range contains duplicate items, the Combo Box will display only one instance of the item.
• Using many Combo Boxes (or large ranges) may have an effect on performance. Specifically, there may be a slight delay before the Data Form is displayed.

Changing the language used in the Enhanced Data Form

By default, the Enhanced Data Form displays text in the English language. You may prefer to customize the data form so it displays a different language. This procedure requires some basic knowledge of the VBA editor.

• NOTE: This modification requires the VBA password, which can be purchased for US $15.00.

To change the language:

1. Activate the VBA Editor.
2. Use the password to unlock the VBA project for the Enhanced Data Form.
3. Activate the ThisWorkbook object.
4. Press F4 to display the Properties box.
5. Set the IsAddIn property to False.
6. Press Alt+F11 to activate Excel.
7. Activate the worksheet (named Sheet1) displayed for the dataform3.xlam workbook.
8. You'll find that column A contains the English text used in the Enhanced Data Form, and column B contains simple descriptions. Enter the translated text into column C
9. Press Alt+F11 to activate the VBA Editor
10. Activate Module1 for The Enhanced Data Form project
11. Change the LANGUAGE constant to 3 (which corresponds to column C)
12. Activate the ThisWorkbook object and set the IsAddIn property back to True
13. Save the workbook

Displaying the Enhanced Data Form using a macro

If you're a VBA programmer, you may want to create a macro that launches the Enhanced Data Form. As long as the add-in file is open, you can use the following VBA statement to display the form:

Application.Run "ShowDataForm"

Using the Enter key to move among the fields

Normally, the Tab key is used to move among the controls in a dialog box, and the Enter key closes the dialog box. Several users have asked me if it's possible to make the Enter key function as the Tab key. I've provided some code that you can add. Note that, after adding this code, you can no longer use the Tab key to activate the buttons on the form. You'll need to use the mouse or the keyboard shortcuts.

• NOTE: This modification requires the VBA password, which can be purchased for US $15.00.

Following are instructions to modify the code:

1. Unprotect the project and activate the code module for the FormMain UserForm.
2. Locate the UserForm_Initialize procedure (it's near the top of the module)
3. Insert these statements following the variable declarations:

    'Allow Enter key to move to next field
    Call SetUpEnterKey

4. Copy and paste these three new procedures into the module (they can go anywhere):

Sub SetUpEnterKey()
    Dim ctl As control
    Application.OnKey "~", "ActivateNextField"
    CloseButton.Default = False
    On Error Resume Next
    'Disable Tab for all controls
    For Each ctl In Me.Controls
       ctl.TabStop = False
    Next ctl
    On Error GoTo 0
    'Avoid selecting the MultiPage tab
    Me.MultiPage1.Pages(0).Cycle = 2
    Me.MultiPage1.Pages(1).Cycle = 2
End Sub

Sub ActivateNextField()
    'Simulate the Tab key when user presses Enter
    Application.SendKeys "{tab}"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Cancel the OnKey event
    Application.OnKey "~", ""
End Sub

Don't forget to save the changes to the add-in.

How to use it

The J-Walk Enhanced Data Form is a general-purpose data entry form that works with any worksheet database. A worksheet database is a range of cells that contain fields (columns) and records (rows). The cells in the database can contain values, text, dates, logical values, or formulas. The first row of the database should contain field names.

Displaying the Enhanced Data Form

• For Excel 2007 Users:
  When the Enhanced Data Form add-in is installed, the Excel 2007 Data tab displays a new group DataForm, and this group contains one icon: J-Walk Enhanced Data Form. Select any cell in your worksheet database table, and then select the Ribbon command.





• For Users of Excel 97 - 2003:
  When the Enhanced Data Form add-in is installed, Excel's Data tab displays a new menu item: J-Walk Enhanced Data Form. Select any cell in your worksheet database table, and then select the Ribbon command.
   

The figure below shows the Enhanced Data Form. The exact configuration depends on the number of fields, the field names, and whether the Data Form has been customized. When the dialog box is displayed, it will show the record that corresponds to the active cell. In addition, the database row will be highlighted in the worksheet.
 



Notice that the dialog box has two tabs: Data and Criteria. The Data tab is used for viewing, editing, and entering data. The Criteria tab is to specify search criteria that will enable you to locate specific records.

Changing the size of the form

You can easily change the size of the Enhanced Data Form. Click the lower right corner of the form and drag. You can make the form taller, shorter, wider, or narrower. The width of the data entry fields change accordingly (but the width of the descriptive field names remain fixed).

To adjust the width of the field labels, see Customizing the Data Form.

Viewing data

The horizontal scroll bar (at the bottom of the Enhanced Data Form) is used to quickly activate a particular record. The label below the scroll bar displays the current record number and the total number of records (for example, Record 195 of 905). You can change the current record (row) by using the horizontal scroll bar, or by using the Previous or Next buttons. The data is displayed in the dialog box, and is also highlighted in the worksheet.

Editing data

To change the data displayed in the Enhanced Data Form, activate the appropriate field and use standard editing techniques. Note that you can use the Tab key (and Shift+Tab) to cycle among the fields. If all of the fields are not visible, use the vertical scroll bar. This scroll bar is not present if all fields are displayed. After you've edited the field(s), click Next or Previous to store the changes in the worksheet. Press Enter (or click Close) to close the dialog box.

Adding new data

To add new data to your worksheet database, click the Insert button or the New button. Clicking the Insert button inserts a new row above the current row. Clicking the New button adds the data to the end of the database table. After you enter the data in the dialog box, add it to the worksheet by clicking Next, Previous, Insert, or New.

When a new record is added, the text [NEW] is entered into the first field. This is done in order to maintain the integrity of the database table.

If your database is an Excel 2007 table (created by using the Insert - Table command), the table is automatically expanded to include new data.

Deleting data

Click the Delete button to delete the current record. Subsequent records will be shifted upward to eliminate the gap caused by the deleted row.

Undoing operations

After you've made a change to your database, you can undo the change by clicking the Undo xxxx button. This button will display the operation that will be undone. For example, it may display Undo Delete. There is only one level of undo. The following operation can be undone:

• Insert
• New
• Delete

Entering search criteria

In some cases, you may want to locate records that meet certain criteria. To enter search criteria, click the Criteria tab of the Enhanced Data Form dialog box. You'll see the same field names listed in the Data tab -- but the background color is different to remind you that you're in the Criteria tab.

Enter the data to find in the appropriate fields. For additional information about searching, click the Tips button.

After you've entered your search criteria, click the Data tab. You'll find that the dialog box has changed in three ways:

• The Next button now displays Find Next. Use this button to display the next record that matches your search criteria.
• The Previous button now displays Find Prev. Use this button to display the previous record that matches your search criteria.
• The Criteria tab now displays <<Criteria>>. This is just a reminder that search criteria are in effect.

To cancel this search mode and return to normal, click the Criteria tab and click the Clear button.

Features

The table below compares the J-Walk Enhanced Data Form with Excel's built-in Data Form*.

Feature	J-Walk Enhanced Data Form 3	Excel's Built-In Data Form
Works with any database table in any worksheet	Yes	Yes
Available in the Excel 2007 Ribbon	Yes, in the Data tab	No. But it can be added to the Quick Access Toolbar.
Maximum number of fields (columns)	Unlimited	32
Sizable	Yes. You can specify the height and width. If the size is too small, the dialog box fields will scroll. In addition, the user can drag the lower right corner to size the dialog box.	Sizes automatically, based on the widest column and number of fields. In some cases, the dialog box may be larger than the entire screen!
Change the width of the field labels	Yes	No
Highlights the current record in the worksheet?	Yes. This makes it very easy to see the actual record that you are editing.	No
Option to display fields as either a Text Box or a drop-down Combo Box?	Yes. This makes it possible to choose data from a list of items.	No, all data is displayed in a Text Box.
Displays the current record at start-up?	Yes	No. Always displays the first record.
Selects the current record when the form is closed?	Yes	No. The worksheet selection is not changed.
Allows insertion of a new record at any row position?	Yes	No. New records are always appended to the end of the database.
Undo available?	Yes	Limited
Language customization?	Yes. Those who purchase the VBA password can easily change the text displayed in the Enhanced Data Form.	No
Handles the apostrophe "prefix character" correctly?	Yes	No. For example, editing a 16-digit credit card number preceded by an apostrophe will cause the credit card number to be converted to a value (with one digit lost).
VBA source code available for customization or learning purposes?	Yes, for a nominal fee.	No. Excel's Data Form is not written in VBA.

* In Excel 2007, Microsoft does not include a direct way to access the built-in data form. To use the built-in data form you must add an icon to your Quick Access Toolbar. Right-click the QAT and choose Customize the Quick Access Toolbar. Then choose Commands Not in the Ribbon from the listbox, and and click Form. Click the Add button to add the icon to your QAT.

The J-Walk Enhanced Data Form

The J-Walk Enhanced Data Form is a FREE Excel add-in that provides a general-purpose data entry dialog box. The Enhanced Data Form adjusts to any database table in any worksheet. It's a significantly enhanced alternative to Excel's built-in Data Form (which is not even part of the user interface in Excel 2007 and later).

Very Important: This add-in comes in two sub-versions, and both are included in the download:

• Version 3a - for Excel 97, 2000, 2002, and 2003
• Version 3b - for Excel 2007 and Excel 2010

This add-in does not work with any version of Excel for Macintosh.



What's new in Version 3?

Click here for a complete list of Enhanced Data Form features. The new features in version 3 are:

• The ability to change the dimensions of the Form dynamically. When the form is displayed,  just click the lower right corner and drag to make the form taller, shorter, wider, and narrower. If you prefer that the user not have this ability, that's also an option.

• The ability to specify a different width for the field names. In the previous version, the field width was always a fixed size. Version 3 can accommodate database tables that have lengthy field names.

• Improved support for non-American date formats.

Pricing

The Enhanced Data Form is free, and is not crippled in any way. It is provided with no strings attached, no nag messages, and no ads. It can be freely distributed and used without a license. However, it may not be sold, or included as part of any other product without the written permission of J-Walk & Associates.

VBA Source Code is Available

The Enhanced Data Form is very flexible, but some users may prefer to customize it for their needs. Others are just curious to see how it works. The VBA project is protected, but you can purchase the password for US $15.00. The password will let you examine every line of code in the project (including the XML code that creates the Ribbon icon in the Excel 2007 version).

• Note: Even though Version 3 has more features, the price for the password has been reduced from the previous $20.