Microsoft KB Archive/49265

{| = Excel: Determining Button Clicked in User-Defined Dialog Box =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q49265

SUMMARY
In a custom dialog box in Microsoft Excel, the DIALOG.BOX function returns a FALSE if the dialog box is canceled and the item number of the OK button if OK is clicked.

To evaluate which OK button is chosen in a dialog box, or if the dialog box is canceled, an IF statement can be used. For example, the following conditional in A1 will react as follows when the dialog in B1:I4 is executed:

Button Clicked   Action --   --

Print            Executes the PRINT command Edit             Runs the macro named &quot;Edit&quot; Cancel           Closes the active window

A                          B  C  D  E  F     G   1   =DIALOG.BOX(B1:I4)                                 Dialog Box 2  =IF(A1=2,PRINT)                  1               Print 3  =IF(A1=3,Edit)                   3               Edit 4  =IF(A1=FALSE,CLOSE)              2               Cancel 5  =RETURN

MORE INFORMATION
In the above example, cell A1 will have a value of 2 if the Print button is clicked, since it is a default OK button and appears as the second item in the dialog box table. A1 will have a value of 3 if the Edit button is clicked, since it is an OK button that appears as the third item in the dialog table. Clicking the Cancel button will return a FALSE to cell A1, and no changes made in the dialog box will be recorded in the initial/result column.

Note that Excel considers any nonzero value to have a Boolean equivalent of TRUE. Therefore, it is possible to nest the DIALOG.BOX statement in an IF statement such that it will do one action if any OK button is clicked, and another if Cancel is clicked. For example, the following macro will call the dialog box again if the Cancel button is clicked and will continue when the OK button is clicked. Thus, it is impossible to leave this dialog box by pressing ESC or clicking Cancel.

A                    B  C  D  E  F     G   1   =DIALOG.BOX(B1:I3)                           Print Dialog Box 2  =IF(A1,PRINT,GOTO(A1))     1               OK   3   =RETURN                    2               Cancel
 * }