Excel AppNote: "Using Dynamic Dialog Boxes" (XE0449)

Last reviewed: February 2, 1998
Article ID: Q110192

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0
Microsoft Excel for the Macintosh, versions 3.0, 4.0

The following is the full text of the Application Note "Using Dynamic Dialog Boxes" (XE0449).

You can obtain this Application Note from the following sources:

Microsoft Download Service (MSDL)
Microsoft Product Support Services

For complete information, see the "To Obtain This Application Note" section at the end of this article.

THE TEXT OF XE0449

   ======================================================================
     Microsoft(R) Product Support Services Application Note (Text File)
                     XE0449: USING DYNAMIC DIALOG BOXES
   ======================================================================
                                                     Revision Date: 12/93
                                                         No Disk Included
   
   The following information applies to Microsoft Excel, versions 3.0,
   and 4.0.
   
   -----------------------------------------------------------------------
   | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
   | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
   | Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
   | EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
   | WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
   | PURPOSE. The user assumes the entire risk as to the accuracy and    |
   | the use of this Application Note. This Application Note may be      |
   | copied and distributed subject to the following conditions: 1) All  |
   | text must be copied without modification and all pages must be      |
   | included; 2) If software is included, all files on the disk(s) must |
   | be copied without modification (the MS-DOS(R) utility diskcopy is   |
   | appropriate for this purpose); 3) All components of this            |
   | Application Note must be distributed together; and 4) This          |
   | Application Note may not be distributed for profit.                 |
   |                                                                     |
   | Copyright (C) 1991-1993 Microsoft Corporation. All Rights Reserved. |
   | Microsoft and MS-DOS are registered trademarks and Windows is a     |
   | trademark of Microsoft Corporation                                  |
   | Macintosh is a registered trademark of Apple Computer, Inc.         |
   |---------------------------------------------------------------------|

OVERVIEW

This Application Note discusses how to create a dynamic dialog box and provides a sample dialog box definition table and sample macro code you can use to activate the dialog box. It also provides an example of one way you can retrieve information from a dynamic dialog box and enter that information in a database.

WHEN TO USE A DYNAMIC DIALOG BOX

When you want to reflect changes that you make within a dialog box without having to remove the dialog box from the screen, you can use a dynamic dialog box along with a control macro. An example of a dynamic dialog box in Microsoft Excel is the Workspace dialog box: when you choose Workspace from the Options menu, the dialog box appears; and when you select the Fixed Decimal check box, the Places check box is automatically selected and the dialog box remains on the screen.

CREATING A CUSTOM DYNAMIC DIALOG BOX

To create a dynamic dialog box, you define items in the dialog box so that they act as "triggers." To make your dynamic dialog box work, you also create macro code that recognizes these triggers and displays and redisplays your dialog box as these items are selected. When you select a dialog box item that is a trigger, your macro continues running and the dialog box remains on the screen.

When you create a dynamic dialog box, you use the same procedure that you use to create the dialog box definition table for a custom dialog box (for more information about creating custom dialog boxes, see pages 263-282 in the "User's Guide 2"). You make the dialog box dynamic by defining items in the dialog box to make them work as trigger items.

TO CREATE A TRIGGER ITEM

To create a trigger item, add 100 to the item type number in the dialog box definition table (this number is located in the first column of the dialog box definition table). For example, to make a list box item act as a trigger, change its item type number from 15 to 115.

  NOTE: The Dialog Editor allows you to set items as triggers when you
  define your dialog box.

All dialog box items can be used as triggers except the following: fixed text (item 5), group boxes (item 14), icons (item 1), and directory text (item 20).

You can also control whether certain options are available or unavailable (dimmed). This feature is useful when you want a certain dialog box item to be unavailable until you choose a related item. To make a dialog box item unavailable, add 200 to the item number. For example, to make an option button (item number 12) unavailable, specify 212 in the item number column of the dialog box definition table.

SETTING UP YOUR COMMAND MACRO

When you create the command macro for a dynamic dialog box, you must set up the initial dialog box and then provide the structure to redisplay the macro when you select a trigger item.

TO SET UP THE INITIAL DIALOG BOX

Determine how the dialog box will appear when it is first displayed. To modify the dialog box items, change the values in the Init/Result column of the dialog box definition table; for example, you can clear or set values in various editable items, enter default text in text edit boxes, and disable or clear items.
Set your trigger items by changing the item numbers in the first column of the dialog box definition table.

TO CREATE A MACRO TO PROVIDE THE STRUCTURE FOR

REDISPLAYING THE DIALOG BOX

To test if a trigger item has been selected, you can use a WHILE-NEXT loop in your macro. In the WHILE-NEXT loop, the exit condition should reflect a choice you make in the dialog box. For example, the WHILE() function could test for a specific value returned by the DIALOG.BOX() function.

Dialog box items are numbered 0, 1, 2, and so on, starting with the first line in the dialog box definition table. The DIALOG.BOX() function will return one of these numbers; your macro should use these numbers to determine which item in the dialog box was selected. Note that the Cancel button (item type 4) always causes the DIALOG.BOX() function to return a value of FALSE. You can then specify any changes you want your macro code to make before the macro redisplays the dialog box.

RULES TO FOLLOW WHEN MAKING CHANGES TO THE DIALOG BOX DEFINITION TABLE

When you make changes to the dialog box definition table, keep the following in mind:

To enable or disable items in the definition table, use the SET.VALUE() function instead of directly adding or subtracting 200 from the item number. (When you use the SET.VALUE() function, you avoid problems in cases where you select the same trigger item twice and 200 is added or subtracted twice from the item number.)
Updating the dialog box will be less noticeable if you call the same dialog box definition table or call one that has dimensions similar to the original box.
Don't use ECHO(FALSE) with a dynamic dialog box. This function may cause problems when your screen is refreshed.

EXAMPLE OF A DYNAMIC DIALOG BOX

Use the following procedures to create a dynamic dialog box and a control macro. The control macro includes sample macro code that uses the FORMULA() function to retrieve information that you enter in the dialog box. The macro takes this information and enters it in a database called DBASE.XLS.

TO CREATE THE DIALOG BOX

In cells D1:K29 of a new macro sheet, enter the following dialog box definition table:

       D      E      F    G      H      I          J                K
 -------------------------------------------------------------------------
  1 |Item # | Item |  x |  y | width |height |    Text        |Init/Result
    |       | Type |    |    |       |       |                |
 -------------------------------------------------------------------------
  2 |  0                        325    335                           4
  3 |  1        5     65    9                   INVOICE - Shipping
    |                                           Information
  4 |  2        5     15   20
  5 |  3        5     14   42                   Invoice #:
  6 |  4        8     97   38   109
  7 |  5        5     14   65                   Customer
  8 |  6        5     14   81                   Name:
  9 |  7        6     97   70   206
 10 |  8        5     12   95
 11 |  9       14      9  111   147     55      Shipping Method
 12 | 10       11                                                      1
 13 | 11      112                               U.S. Mail
 14 | 12      112                               Next Day Air
 15 | 13      205    167  111                   Overnight Carrier:
 16 | 14      206    165  141   141
 17 | 15      113     14  174                   Address Different    FALSE
    |                                           from Previous?
 18 | 16      205     65  196                   New Shipping Address
 19 | 17      205     11  209
 20 | 18      205      9  229                   Street
 21 | 19      206     61  227   248
 22 | 20      205     22  258                   City
 23 | 21      206     61  253    93
 24 | 22      205    168  258                   State
 25 | 23      206    216  253    93
 26 | 24      205    146  280                   Zip Code
 27 | 25      208    216  279    93
 28 | 26        1      5  307   151             Enter Information
 29 | 27        2    165  307   151             Cancel Request
 -------------------------------------------------------------------------

   Note that column D is not part of the dialog box definition table.
   This column is provided in this example to help identify the item
   numbers. For example, item numbers 11, 12, and 15 are triggers in
   this dialog box. Items 13, 14, and 16 through 25 are unavailable
   (or dimmed).

Select cells E2:K29.
From the Formula menu, and choose Define Name.
In the Name box, type BOX. (Naming the table will make it easier to refer to the dialog box definition table when you write your macro.)

To view your dialog box:

    a. Type the following commands in cells C1 and C2:

          =DIALOG.BOX(box)
          =RETURN()

    b. Select cell C1 and choose Run from the Macro menu.

    c. Choose OK.

Your dialog box should resemble the following example (depending on your screen resolution, the position of the items in the dialog box will vary).

[Graphic deleted]

  NOTE: Your dialog box should resemble the example above. If it does
  not, make sure that your dialog box definition table matches the one
  previously shown.

To remove the dialog box from your screen, choose the Cancel Request button.

TO CREATE A COMMAND MACRO

In column A, type the following command macro:
```
      |  A
```
------------------------------------------------------------------
```
    1 |  dialog_box_macro
    2 |  =SET.VALUE(K2:K29,"")
    3 |  =SET.VALUE(K2,4)
    4 |  =SET.VALUE(K12,1)
    5 |  =SET.VALUE(K17,FALSE)
    6 |  finished=FALSE
    7 |  =WHILE(finished=FALSE)
    8 |  =IF(K12=1,SET.VALUE(E15:E16,{205;206}))
    9 |  =IF(K17=FALSE,SET.VALUE(E18:E27,{205;205;205;206;205;206;
      |  205;206;205;208}))
```
10 | dialog_box=DIALOG.BOX(box) 11 | =IF(dialog_box=FALSE,RETURN()) 12 | =IF(dialog_box=12) 13 | =SET.VALUE(E15:E16,{5;6}) 14 | =SET.VALUE(K2,14) 15 | =ELSE.IF(dialog_box=15) 16 | =SET.VALUE(E18:E27,{5;5;5;6;5;6;5;6;5;8}) 17 | =SET.VALUE(K2,19) 18 | =ELSE.IF(dialog_box=26) 19 | finished=TRUE 20 | =END.IF() 21 | =NEXT() 22 | =RETURN()
To run this macro, select cell A1 and choose Run from the Macro menu.

When you choose a certain shipping method, you are prompted for more information. Also, if you specify a change of address, you will be prompted for the new address specifics.

If you select the "U.S. Mail" or "Next Day Air" button or the "Address Different from Previous?" check box, the macro will continue to execute but the dialog box will remain on the screen. The macro then uses the DIALOG.BOX() function to determine which trigger was selected, performs a task based on that selection, and then redisplays the dialog box.

TO RETRIEVE INFORMATION FROM THE DIALOG BOX AND PLACE IT IN A DATABASE

To retrieve information entered in the dialog box and place it in a database, you need to add more commands to your command macro.

To add macro code that will take information that you enter in the Invoice and Customer Name boxes, enter the information in a database, and redefine the database to include the new information, do the following:

In cell A22, delete the RETURN() function.
Starting in cell A22, add the following code to your macro:
```
      | A
```
------------------------------------------------------------- 22 | =ACTIVATE("dbase.xls") 23 | first_cell=TEXTREF(GET.CELL(1,DBASE.XLS!Datab
```
      | ase),TRUE)
```
24 | =FORMULA(DDBOX.XLM!K6,OFFSET(first_cell,ROWS(
```
      | !Database),0))
```
25 | =FORMULA(DDBOX.XLM!K9,OFFSET(first_cell,ROWS(
```
      | !Database),1))
```
26 | =SELECT(OFFSET(first_cell,0,0,ROWS(!Database)
```
      | +1,2))
```
27 | =SET.DATABASE() 28 | =SELECT(first_cell) 29 | =RETURN()
From the File menu, choose Save. Name your document DDBOX.XLM.
From the File menu, choose New and select Worksheet. Choose OK.
In cell A1 in the new worksheet, type INVOICE NUMBER and in cell B1, type CUSTOMER.
Select cells A1:B1.
From the Data menu, choose Set Database and save the worksheet with the database as DBASE.XLS.
From the Window menu, choose DDBOX.XLM.
Select cell A1 and choose Run from the Macro menu.

When you type a number in the Invoice number box, type a name in the Customer Name box, and choose Enter Information, the information is entered in the database (DBASE.XLS) and your database range is automatically extended to include the new information.

  NOTE: To gain a better understanding of how the command macro works
  with the dynamic dialog box, you can run the macro by choosing the
  Run command from the Macro menu and choosing the Step button.

Line-by-Line Description of the Macro

The following is a line-by-line description of the macro:

 -----------------------------------------------------------------------
 A1:  |  dialog_box_macro--Names the macro.
 -----------------------------------------------------------------------
 A2:  |  =SET.VALUE(K2:K29,"")--Clears the values from the Init\Result
      |  column.
 -----------------------------------------------------------------------
 A3:  |  =SET.VALUE(K2,4)--Sets the default dialog box selection (where
      |  the insertion point appears in the dialog box) to item 4, the
      |  Invoice # text box.
 -----------------------------------------------------------------------
 A4:  |  =SET.VALUE(K12,1)--Sets the shipping method to 1 (U.S. Mail).
 -----------------------------------------------------------------------
 A5:  | =SET.VALUE(K17,FALSE)--Sets the Address Different from Previous
      |  check box to FALSE (not selected).
 -----------------------------------------------------------------------
 A6:  |  finished=FALSE--Sets the variable <finished> to FALSE. This
      |  variable is used to determine when the Enter Information
      |  command button is chosen.
 -----------------------------------------------------------------------
 A7:  |  =WHILE(finished=FALSE)--Start of WHILE loop. This loop
      |  continues until the Enter Information button is chosen
      |  (finished=TRUE).
 -----------------------------------------------------------------------
 A8:  |  =IF(K12=1,SET.VALUE(E15:E16,{205;206}))--If the U.S. Mail
      |  option button is selected, the Overnight Carrier text box is
      |  made unavailable (dimmed) by adding 200 to the items in cells
      |  E15 and E16 in the dialog box definition table.
 -----------------------------------------------------------------------
 A9:  |  =IF(K17=FALSE,SET.VALUE(E18:E27,{205;205;205;206;205;206;205;20
      |  6;205;208}))--If the Address Different from Previous? check box
      |  is not selected, the New Address fields (that is, Street, City,
      |  State, and Zip) are made unavailable.
 -----------------------------------------------------------------------
 A10: |  dialog_box=DIALOG.BOX(box)--Displays the dialog box again with
      |  the above settings.
 -----------------------------------------------------------------------
 A11: |  =IF(dialog_box=FALSE,RETURN())--Checks to see if the Cancel
      |  Request command button is chosen and, if it has been (TRUE),
      |  the macro stops running. (You can also use the BREAK() function
      |  to break out of the WHILE loop but continue with the rest of
      |  the macro.)
 -----------------------------------------------------------------------
 A12: |  =IF(dialog_box=12), A13: =SET.VALUE(E15:E16,{5;6}), A14:
      |  =SET.VALUE(K2,14)--If trigger item 12 (that is, the Next Day
      |  Air option button) is chosen, the Overnight Carrier text and
      |  text edit box is selected by subtracting 200 from item numbers
      |  13 and 14 (in cells E15:E16). The default dialog box selection
      |  is set to item 14.
 -----------------------------------------------------------------------
 A15: |  =ELSE.IF(dialog_box=15)
 -----------------------------------------------------------------------
 A16: |  =SET.VALUE(E18:E27,{5;5;5;6;5;6;5;6;5;8})
 -----------------------------------------------------------------------
 A17: |  =SET.VALUE(K2,19)--If trigger item 15 (that is, the Address
      |  Different from Previous? check box) is selected, the text field
      |  and text boxes for items in cells E18 through E27 are selected
      |  (enabled) by subtracting 200 from the item number. Also, the
      |  default dialog box selection is set to item 19.
 -----------------------------------------------------------------------
 A18: |  =ELSE.IF(dialog_box=26) and A19: finished=TRUE--If the Cancel
      |  Request command button is chosen, the variable <finished> is
      |  set to TRUE.
 -----------------------------------------------------------------------
 A20: |  =END.IF()--Ends the nested IF statement.
 -----------------------------------------------------------------------
 A21: |  =NEXT()--End of WHILE loop. Go back to the beginning of the
      |  loop unless <finished> is set to TRUE.
 -----------------------------------------------------------------------
 A22: |  =ACTIVATE("dbase.xls")--Activates the worksheet with the
      |  database range.
 -----------------------------------------------------------------------
 A23: |  first_cell=TEXTREF(GET.CELL(1,DBASE.XLS!Database),TRUE)--
      |  GET.CELL() finds the cell reference of the upper-left cell in
      |  the database range and returns it as text. The TEXTREF()
      |  function converts this string to a usable reference.
      | <First_cell> is a variable set equal to this reference.
 -----------------------------------------------------------------------
 A24: |  =FORMULA(DDBOX.XLM!K6,OFFSET(first_cell,ROWS(!Database),0))--
      |  Takes the invoice number from K6 in the dialog box definition
      |  table and places it in the first row below the database. The
      |  offset function places the information from K6 in the cell
      |  offset from <First_cell>by the number of rows in the database.
 -----------------------------------------------------------------------
 A25: |  =FORMULA(DDBOX.XLM!K9,OFFSET(first_cell,ROWS(!Database),1))--
      |  Takes the name from K9 in the dialog definition table and
      |  places it in the first row below the database. The OFFSET()
      |  function places the information from K9 in the cell offset from
      |  <First_cell>by the number of rows in the database and 1 column.
 -----------------------------------------------------------------------
 A26: |  =SELECT(OFFSET(first_cell,0,0,ROWS(!Database)+1,2))--Selects a
      |  range that is the height plus one row and width of the
      |  database.
 -----------------------------------------------------------------------
 A27: |  =SET.DATABASE()--Redefines the database range.
 -----------------------------------------------------------------------
 A28: |  =SELECT(first_cell)--Selects the first cell.
 -----------------------------------------------------------------------
 A29: |  =RETURN()--Ends the macro.
 -----------------------------------------------------------------------

FINDING MORE INFORMATION

For more information about dynamic dialog boxes, see the appropriate manual and page numbers below.

   For this version
   of Microsoft Excel    See this reference
   ---------------------------------------------------------------------
   
   Version 4.0           For more information about creating custom
                         dialog boxes, see pages 263-282 of the "User's
                         Guide 2."
                         For more information about creating dynamic
                         dialog boxes, see pages 280-281 of the "User's
                         Guide 2."

   Version 3.0 for       For more information about creating custom
   Windows               dialog boxes, see pages 624-638 of the "User's
                         Guide."
                         For more information about creating dynamic
                         dialog boxes, see pages 639-641 of the "User's
                         Guide."
   
   Version 3.0 for       For more information about creating custom
   the Macintosh         dialog boxes, see pages 621-637 of the "User's
                         Guide."
                         For more information about creating dynamic
                         dialog boxes, see pages 635-642 of the "User's
                         Guide."
   
   --------------------------------------------------------------------

TO OBTAIN THIS APPLICATION NOTE

Application Notes are available by modem from the Microsoft Download Service (MSDL), which you can reach by calling (425) 936-6735. This service is available 24 hours a day, 7 days a week. The highest download speed available is 14,400 bits per second (bps).
For more information about using the MSDL, call (800) 936-4100 and follow the prompts. To obtain XE0449, download XE0449.TXT.
If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (425) 635-7070 (Windows) or (425) 635-7080 (Macintosh). If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:
```
      http://www.microsoft.com/worldwide/default.htm
```

Additional query words: 3.00 4.00 4.00a
Keywords : kbappnote kbfile kbprg
Version : WINDOWS:3.0,4.0,4.0a; MACINTOSH:3.0,4.0
Platform : MACINTOSH WINDOWS
Issue type : kbinfo

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: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.