The Spreadsheet Page - Data Form Customizing

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 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
  1. 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.

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy