Excel: Determining Button Clicked in User-Defined Dialog Box

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


KBCategory: kbother
KBSubcategory:

Additional reference words: 1.50 2.20 3.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.