J-Walk Enhanced Data Form v2

Return to The Spreadsheet Page

Enhanced Data Form Home

Features

How to use it

Customizing the Data Form

FAQ

Free download

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 increase the size of the dialog box by creating one or two names in your workbook.

To change the width of the Enhanced Data Form:

  1. Activate the workbook that contains a database.
  2. Select Insert - Name - Define to display the Define Name dialog box.
  3. In the Names in workbook field, enter DF_WIDTH (uppercase or lowercase)
  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 Add 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 Insert - Name - Define to display the Define Name dialog box.
  3. In the Names in workbook field, enter DF_HEIGHT (uppercase or lowercase)
  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 Add to add the name to the workbook.

After adding these names, the Enhanced Data Form will be displayed using the new dimensions. Note that you can create one name or both names. In some cases, a bit of trial and error may be necessary to fine-tune the dialog box dimensions.

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 corresponds 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:

  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, and choose Insert - Name - Define to display the Define Name dialog box.
  4. In the Names in workbook field, enter Region. This name must correspond to a field name in the first row of your database.
  5. Click Add 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

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 $20.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 dataform.xla workbook.
  7. You'll find that column A contains the English text used in the Enhanced Data Form. Enter the translated text into column B
  8. Press F11 to activate the VBA Editor
  9. Activate Module1 for The Enhanced Data Form project
  10. Change the LANGUAGE constant to 2
  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 dataform2.xla file is open, you can use the following VBA statement to display the form:

Application.Run "dataform2.xla!ShowDataForm"