Creating a Custom Data Form from a Macro in Excel

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
• Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

The custom data form in Microsoft Excel is limited to two types of dialog items: static text and edit boxes.

If you want to include other types of items, such as list boxes or option buttons, you must create a macro to duplicate the function of the data form. This article describes a basic macro that allows you to add records to an existing database using a custom dialog box, which acts as a custom data form. To use this macro, you must have a working knowledge of creating and using dialog boxes and have a worksheet with a defined database on it.

MORE INFORMATION

The macro is listed below with an explanation of each step following.

Warning: Read through this article before attempting to type this macro because the macro will need to be adjusted to fit your individual needs.

Sample Macro

A1: Custom_Form A2: =IF(ISERR(GET.NAME("!Database")),ALERT("Database not defined",3)) A3: =IF(ISERR(GET.NAME("!Database")),GOTO(A8)) A4: =DIALOG.BOX(dialog_ref) A5: =IF(A4=FALSE,GOTO(A8)) A6: =FORMULA(initresult,OFFSET(!Database,ROWS(!Database),N,1,1)) . . (similar FORMULA statements) . A7: =DEFINE.NAME("Database",OFFSET(!Database,0,0,ROWS(!Database)+1,

       COLUMNS(!Database)))

Explanation

1. Steps A2 and A3 check to see whether or not there is a database defined on the active worksheet. If there is not, an alert message is displayed and the macro is halted.

2. Step A4 displays the dialog box or custom data form, and allows you to enter information for a new record for the database. The name "dialog_ref" refers to the seven-column dialog definition table on your macro sheet.

3. Step A5 tests to see whether the Cancel button was chosen in the dialog box. If so, the macro is halted.

4. Step A6, and all Formula statements following it, enter your new record into the defined Database on your worksheet. You should have as many Formula statements as you have field names in your database. "Initresult" is a reference (for example, J12) and refers to the first item in the Init/Result column of "dialog_ref" that you want placed in the database. "N" represents the column number in your database (beginning with zero) in which you want each item placed. For example, if "N" is replaced with 1 in the FORMULA function above, the item will be placed in the second column of your database.

5. Step A7 renames your database to include the new record.

Another option for using your custom data form is to modify the Data Form command in Microsoft Excel so that it runs your Custom Data Form macro. To do this, you should create and define the name "Auto_open" on your worksheet so that it refers to a macro similar to the following:

   A13:  =DELETE.COMMAND(1,"Data","Form...")
   A14:  =ADD.COMMAND(1,"Data",A17:B17,1)
   A15:  =RETURN()

   A17:  Custom_Form                          B17:  R1C1

REFERENCES

"Microsoft Excel User's Guide," for Windows and OS/2, version 3.0, pages 348-368, 624-640