Excel: Determining Button Clicked in User-Defined Dialog Box

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 "Edit"
   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