J-Walk Enhanced Data Form v3

Return to The Spreadsheet Page

Enhanced Data Form Home

Features

How to use it

Customizing the Data Form

FAQ

Free download

Support

Purchase VBA password

 

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

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 makes 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 - NEW FEATURE

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. Set the IsAddIn property to False.
  5. Press F11 to activate Excel.
  6. Activate the worksheet (named Sheet1) displayed for the dataform3.xlam workbook.
  7. 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
  8. Press F11 to activate the VBA Editor
  9. Activate Module1 for The Enhanced Data Form project
  10. Change the LANGUAGE constant to 3 (which corresponds to column C)
  11. Activate the ThisWorkbook object and set the IsAddIn property back to True
  12. 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"