Microsoft KB Archive/73387

{| = Creating a Custom Data Form from a Macro in Excel =
 * width="100%"|

Last reviewed: November 4, 1994

Article ID: Q73387 The information in this article applies to:
 * 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(&quot;!Database&quot;)),ALERT(&quot;Database not defined&quot;,3)) A3: =IF(ISERR(GET.NAME(&quot;!Database&quot;)),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(&quot;Database&quot;,OFFSET(!Database,0,0,ROWS(!Database)+1,

COLUMNS(!Database))) A8: =RETURN

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 &quot;dialog_ref&quot; 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. &quot;Initresult&quot; is a reference (for example, J12) and refers to the first item in the Init/Result column of &quot;dialog_ref&quot; that you want placed in the database. &quot;N&quot; represents the column number in your database (beginning with zero) in which you want each item placed. For example, if &quot;N&quot; 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.

After creating the macro, select cell A1 (or the first cell of your macro), choose Define Name from the Formula menu and choose the Command button. Choose OK. You need to run this macro from the worksheet. You can run the macro by choosing Run from the Macro menu and selecting the macro from the list, or by using a hot key that you have assigned to the macro (see the dialog box that appears when you choose Define Name from the Formula menu). 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 &quot;Auto_open&quot; on your worksheet so that it refers to a macro similar to the following:

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

A17: Custom_Form                          B17:  R1C1