Excel AppNote: "Introduction to Custom Dialog Boxes" (XE0938)

Last reviewed: February 2, 1998
Article ID: Q111350

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a
Microsoft Excel for the Macintosh, version 4.0

The "Introduction to Custom Dialog Boxes" (XE0938) Application Note shows you how to use the Dialog Editor to create a custom dialog box and provides sample macro code to display it.

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 XE0938

   --------------------------------------------------------------------
   
   ======================================================================
     Microsoft(R) Product Support Services Application Note (Text File)
                 XE0938: INTRODUCTION TO CUSTOM DIALOG BOXES
   ======================================================================
                                                   Revision Date: 2/94
                                                      No Disk Included
   
   The following information applies to Microsoft Excel, version 4.0.
   
   -----------------------------------------------------------------------
   | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
   | ACCOMPANY THIS DOCUMENT (collectively referred to as a              |
   | 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; 3) All          |
   | components of this Application Note must be distributed together;   |
   | and  4) This Application Note may not be distributed for profit.    |
   |                                                                     |
   | Copyright © 1993-1994 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 shows you how to use the Dialog Editor to create a custom dialog box and provides sample macro code to display it.

INTRODUCTION

In order to create a custom dialog box, you need to create a dialog box definition table. To use the custom dialog box, you must write a macro or series of macros that will set the initial values (this is called "initializing" the dialog box), display the dialog box, and return entered information.

CREATING THE DIALOG BOX DEFINITION TABLE

Although it is possible to create a dialog box directly on a macro sheet, it is easier to visualize the dialog box when you use the Dialog Editor. The Dialog Editor allows you to move, size, and delete dialog box items. The Dialog Editor also creates a dialog box definition table: a table that contains information about the items in your dialog box.

To start the Dialog Editor

In Windows, press ALT+SPACEBAR to display the Control menu.
From the Control menu, choose Run.
In the Run dialog box, select the Dialog Editor option and choose OK.
-or-
On the Macintosh(R), double-click the Dialog Editor icon in the folder containing Microsoft Excel.

The default window in the Dialog Editor contains an empty dialog box similar to the one in Figure 1.

<graphic deleted> Figure 1 - Default dialog box in the Dialog Editor

To add an item to a dialog box

Select the Item menu to display a list of available items.
Choose the item you want to add.

For a description of all dialog box items, see pages 274-279 in "User's Guide 2."

The first item you add will be placed in the top left corner of the Dialog Editor. Subsequent additions are placed below the most recently entered item.

To move and size an item

To move an item, use the mouse to select it and drag the item to a different area of the dialog box.

-or-

Double-click an item to bring up the Info dialog box. You can move and size items in the Info Dialog Box by changing the values in the X, Y, Width, and Height boxes. For example, in the Info box below you could change the position of the OK button by changing the values in the X and Y boxes.

  NOTE: You must clear the Auto check box before you can move or size
  some items. For example, in the Default OK Info box below, you
  would have to clear the Auto check box by Height in order to change
  the height of the OK button.

<graphic deleted> Figure 2 - Info dialog box for OK button

For more information about working with items in a dialog box, see pages 264-266 of the "User's Guide 2."

CREATING A REGISTRATION FORM DIALOG BOX

The following procedures help you create a dialog box similar to the one in Figure 3.

To create this dialog box, you must specify the dialog box items in the Dialog Editor and copy the dialog box definition table to a macro sheet. Before you run the dialog box, you may want to create a macro to set the values that will be displayed when you initially run the dialog box. Finally, you need to create a macro to display the dialog box.

<graphic deleted> Figure 3 - Registration Form dialog box

CREATING THE DIALOG BOX DEFINITION TABLE FOR THE REGISTRATION FORM

To add the Name text item and text edit box

In the Dialog Editor, choose Text from the Item menu.
When the text box appears, type NAME: (do not press ENTER).
From the Item menu, choose Edit Box, and select the Text option. Choose OK.
Position the text edit box to the right of Name:.

To increase the size of the dialog box

To increase the size of the dialog box, position the insertion point along any edge of the box until it becomes a two-headed arrow. Drag the arrow to size the window.

To add the Classification text item and standard list box

From the Item menu, choose Text.
When the text box appears, type CLASSIFICATION: (do not press ENTER).
From the Item menu, choose List Box, and select Standard. Choose OK.

Position the list box and text so that they resemble the Registration Form dialog box in Figure 3 on page 2 of this Application Note.

To create the Class Choices group and option buttons

The class choices options are located in a group box that contains option buttons. Grouping items is useful when you have items that are related. In this example, the classes, which are related, are placed in a group box: this means the user can select only one class at a time. Grouped items are moved and sized together.

To add a group of five option buttons and text items for Calculus, Scuba Diving, Bus. Communications, Systems Development, and Corporate Finance, do the following:

From the Item menu, choose Group.
When the group box appears, type CLASS CHOICES: (do not press ENTER).
From the Item menu, choose Button and select Option. Choose OK.
When the Option button appears, type CALCULUS (do not press ENTER).
Repeat steps 3 and 4 for each of the option buttons.

For more information about working with groups of items, see pages 266-267 of "User's Guide 2."

To add the OK and Cancel buttons

From the item menu, choose Button. From the group of options, select the OK option and then choose the OK button.
Repeat step 1 to add the Cancel button (but select Cancel instead of OK).

COPYING THE DIALOG BOX DEFINITION TABLE TO A MACRO SHEET

Once you have created the dialog box in the Dialog Editor, you need to copy it to a macro sheet.

To copy the dialog box from the Dialog Editor to a macro sheet

In the Dialog Box editor, choose Select Dialog from the Edit menu.
From the Edit menu, choose Copy.
Switch to Microsoft Excel:
In Windows, display the Task List by pressing CTRL+ESC. Select Microsoft Excel from the list, and then choose Switch To.
-or-
On the Macintosh, choose Microsoft Excel from the Application menu, or double-click the Microsoft Excel program icon.
In Microsoft Excel, choose New from the File menu, and select the Macro Sheet option. Choose OK.

Select cell C2 (this cell will be in the upper-left corner of the dialog box definition table).

     NOTE: It is helpful to paste your definition table above where you
     plan to write your code (this leaves you less chance of inserting
     or deleting rows within the table).

From the Edit menu, choose Paste.

Immediately after you paste the table, while the area is still highlighted, choose Define Name from the Formula menu. In the Name box, type a name for your dialog box. (For this example type REGBOX.)

     TIP: To be sure that you do not leave out any rows or columns, name
     the definition table immediately after you paste it into the macro
     sheet (while it is still highlighted).

For information on defined names, see "More About Naming a Cell, Range, or Formula" on page 268 in "User's Guide 1."

     NOTE: Later, if you need to change your dialog box, you can paste
     the dialog box definition table back into the Dialog Editor. To do
     this, choose Go To from the Formula menu and select the name of
     your dialog box. Choose OK to select your entire dialog box.

While the definition table is still highlighted, create a border around the definition table to make it easier to see. To create a border, choose Border from the Format menu. Choose the Outline Border tool and then choose OK.
Label the columns of the definition table. This will make it easier to locate the cells to reference when working in your macro. The column headings are Item, X, Y, Width, Height, Text, and Init/Result (to see what these headings look like, see the sample dialog box definition table in Figure 4).

When you paste the dialog box into a macro sheet, you create a dialog box definition table. Your dialog box definition table should resemble the example in Figure 4.

      |  C   | D  | E  |  F    |   G    |      H          |    I
   1  | Item | X  | Y  | Width | Height |     Text        |Init/Result
   --------------------------------------------------------------------
   2  |                   538     194     Registration Form
   3  |   5    11   17                    Name:
   4  |   6    73   16    160
   5  |   5    13   52                    Classification:
   6  |  15    73   72    160      84
   7  |   1   278  151     88             OK
   8  |   2   406  150     88             Cancel
   9  |  14   279   17    238     111     Class Choices
  10  |  11
  11  |  12                               Calculus
  12  |  12                               Scuba Diving
  13  |  12                               Bus. Communications
  14  |  12                               Systems Development
  15  |  12                               Corporate Finance
  ----------------------------------------------------------------------

Figure 4 - The dialog box definition table for the Registration Form dialog box

The dialog box definition table contains seven columns and as many rows as there are items in your dialog box plus one additional row. This extra row defines the height and width of the overall box. The range of the definition table in Figure 4 is C2:I15. The first column of the definition table lists the item numbers of the items in your dialog box. Columns 2-5 give the dimensions of the items, and column 6 is the text displayed in the items. Column 7, the Init/Result column, is where information about what you enter and select in the dialog box is returned. If you enter the column headings in C1:I1, you can easily identify each column.

For more information about dialog box items and their item numbers, see pages 274-279 in "User's Guide 2."

INITIALIZING THE DIALOG BOX

To create a title bar

You can make your dialog box look more like the built-in Microsoft Excel dialog boxes by creating a title bar. To create a title bar, enter the title in the text column of the first row of the definition table (cell H2 in this example). The title of the dialog box in Figure 4 is Registration Form. Creating a title bar allows you to move your dialog box around on the screen. Without the title bar, the box is unmovable or "static."

For more information about changing an existing dialog box, see page 269 in "User's Guide 2."

To initialize the dialog box

You can set the initial values in the Init/Result column of the dialog box definition table with the SET.VALUE() macro command.

You may want to set items that return information to the Init/Result column to null before you run the dialog box. For example, you may want to clear this column for items that have more than one option to select (such as a multiple-selection list box) or one-option items (such as the Name edit box). To clear items that have more than one option to select, use the #N/A value; to clear items that have only one option, use two quotation marks (""). The sample macro code in Figure 5 sets the edit box, list box, and group boxes to null.

     |             A             |     B
     |  Registration_Dialog_Box  |  Comments
   -------------------------------------------------------------
   1 |  =SET.VALUE(I4,"")           Sets edit box to null
   2 |  =SET.VALUE(I6,#N/A)         Sets list box to null
   3 |  =SET.VALUE(I10,#N/A)        Sets option group to null
   -------------------------------------------------------------

Figure 5 - Sample macro code to set initial values

IMPORTANT: When you enter macro code in columns A and B, be careful when you insert entire rows or shift columns or cells. Inserting an entire row or shifting columns or cells may change the dialog box definition table (which starts in cell C2 in this example) and may cause your dialog box macro to fail.

To add items to a list box

To add items to a list box, first enter the items in a cell on a worksheet or macro sheet. To make the text appear in the box, reference the list in the Text column for the appropriate list box. This reference can be in R1C1 reference style, a defined name, or a defined array. In this example, the list items are in cells K2 to K5 on the macro sheet and are defined as <list>.

    |  C   | D | E |   F   |   G    |        H      |     I     |J|  K
  1 | Item | X | Y | Width | Height |      Text     |Init/Result| | List
  ----------------------------------------------------------------------
  2 |                 538     194    Registration Form           Freshman
  3 |   5    11  17                  Name:                      Sophomore
  4 |   6    73  16   160                                          Junior
  5 |   5    13  52                  Classification:               Senior
  6 |  15    73  72   160      84    R2C11:R5C11           2
  7 |   1   278 151    88            OK
  8 |   2   406 150    88            Cancel
  9 |  14   279  17   238     111    Class Choices
 10 |  11                                                  4
 11 |  12                            Calculus
 12 |  12                            Scuba Diving
 13 |  12                            Bus. Communications
 14 |  12                            Systems Development
 15 |  12                            Corporate Finance
 ------------------------------------------------------------------------

Figure 6 - The updated dialog box definition table

CREATING MACROS THAT DISPLAY YOUR DIALOG BOX AND RETURN ENTERED VALUES

To display your dialog box

To view the dialog box, you need to create a macro that uses the DIALOG.BOX() macro command. The syntax of DIALOG.BOX() is DIALOG.BOX (<dialog_ref>), where <dialog_ref> is a reference to the dialog box definition table. This reference can be a defined name or a cell range. If you use the name that you define immediately after pasting your dialog box into the macro sheet from the Dialog Editor, you can eliminate the possibility of leaving out a row or column.

To display the Registration Form dialog box, enter the following macro code starting in cell A1:

      |            A            |    B
   1  | Registration_Dialog_Box | Comments
  ----------------------------------------------------------
   2  | =SET.VALUE(I4,"")         Sets edit box to null
   3  | =SET.VALUE(I6,#N/A)       Sets list box to null
   4  | =SET.VALUE(I10,#N/A)      Sets option group to null
   5  | =DIALOG.BOX(RegBox)       Displays dialog box
   6  | =RETURN()                 Ends macro
  ----------------------------------------------------------

To run the macro:

Define the macro by selecting cell A1 (which contains the Registration_Dialog_Box macro).
From the Formula menu, choose Define Name. Under Macro Options, select Command, and then choose OK.
From the Macro menu, choose Run. Select the Registration_Dialog_Box macro, and then choose OK.

To redisplay the dialog box definition

table until the Cancel button is chosen

When you use the macro you created in the procedure on page 5 to run your dialog box, every time you want to add a new name in the Name box you have to rerun the macro. To redisplay the dialog box after each entry until the user chooses the Cancel button, put the code that displays the dialog box in a loop, such as a WHILE-NEXT loop. The code in Figure 7 is an example of the macro with this added code.

      |             A             |     B
   1  | Registration_Dialog_Box   | Comments
  ----------------------------------------------------------
   2  | =SET.VALUE(I4,"")           Sets edit box to null
   3  | =SET.VALUE(I6,#N/A)         Sets list box to null
   4  | =SET.VALUE(I10,#N/A)        Sets option group to null
   5  | =SET.NAME("result", TRUE)   Defines variable <result>
      |                             and sets it to TRUE
   6  | =WHILE(result<>FALSE)       Tests value of <result>
   7  | result=DIALOG.BOX(regbox)   Displays dialog box
   8  | =SET.VALUE(I4,"")           Sets edit box to null
   9  | =SET.VALUE(I6,#N/A)         Sets list box to null
  10  | =SET.VALUE(I10,#N/A)        Sets option group to null
  11  | =NEXT()                     Ends WHILE-NEXT loop
  12  | =RETURN()                   Ends macro
  ----------------------------------------------------------

Figure 7 - Macro code to display dialog box until Cancel button is chosen

When result is set to TRUE in A5, the dialog box is displayed at least once. If the user chooses OK, <result> is a number. When the user chooses Cancel, <result> is FALSE. The WHILE statement in A6 checks the value of result to make sure it is not equal to FALSE. If the value of <result> is not FALSE, the dialog box is displayed again, with all items set to null. The items are reset to null with the statements in cells A8:A10 (see A8:A10 in Figure 7).

Using INDEX() to Return List Selections

When you select options from a list box or group box, numbers are returned to the Init/Result Column. These numbers represent the position of the selected item. In this example, when Sophomore is selected for Class selection, 2 is returned to cell I6 because the Sophomore Classification is the second item in the list. When Systems Development is selected, the number 4 is returned to cell I11 because Systems Development is the fourth option in the group.

       C     D   E    F        G            H             I     |J|  K
  1 | Item | X | Y | Width | Height |      Text     |Init/Result| | List
  ----------------------------------------------------------------------
  2 |                 538     194    Registration Form           Freshman
  3 |   5    11  17                  Name:                      Sophomore
  4 |   6    73  16   160                                          Junior
  5 |   5    13  52                  Classification:               Senior
  6 |  15    73  72   160      84    R2C11:R5C11              2
  7 |   1   278 151    88            OK
  8 |   2   406 150    88            Cancel
  9 |  14   279  17   238     111    Class Choices
 10 |  11                                                     4
 11 |  12                            Calculus
 12 |  12                            Scuba Diving
 13 |  12                            Bus. Communications
 14 |  12                            Systems Development
 15 |  12                            Corporate Finance
 -----------------------------------------------------------------------

Figure 8 - Dialog box definition table

To return the actual text of the choice, use the INDEX() function. The syntax of INDEX() is INDEX(<reference>, <rownum>), where the <reference> argument is the range containing the complete list, and rownum is a reference to the cell in the Init/Result column containing the value returned to the definition table. For this example, you need two INDEX() statements because two items are selected. The following is the macro with the added code:

                   A                   B
   1  | Registration_Dialog_Box   | Comments
  -------------------------------------------------------------------
   2  | =SET.VALUE(I4,"")           Sets edit box to null
   3  | =SET.VALUE(I6,#N/A)         Sets list box to null
   4  | =SET.VALUE(I10,#N/A)        Sets option group to null
   5  | =SET.NAME("result", TRUE)   Defines variables <result> and
      |                             sets it to TRUE
   6  | =WHILE(result<>FALSE)       Tests value of <result>
   7  | result=DIALOG.BOX(REGBOX)   Displays dialog box
   8  | =INDEX(H11:H15,I10)         Indexes the items in the group box
   9  | =INDEX(K2:K4,I6)            Indexes the items in the list box
  10  | =ALERT("Student "&I4&",     Displays an alert box
      | whose classification is
      | "&A9&", is registered for
      | "&A8&".")
  11  | =SET.VALUE(I4,"")           Sets edit box to null
  12  | =SET.VALUE(I6,#N/A)         Sets list box to null
  13  | =SET.VALUE(I10,#N/A)        Sets option group to null
  14  | result=DIALOG.BOX(REGBOX)   Displays dialog box
  15  | =NEXT()                     Ends WHILE-NEXT loop
  16  | =RETURN()                   Ends macro
  -------------------------------------------------------------------

Figure 9 - Macro code to display text of list selections

MORE INFORMATION

For more information about creating and using dialog boxes in Microsoft Excel, see the Application Note "Using Dynamic Dialog Boxes" (XE0449).

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 XE0938, download XE0938_W.EXE (Windows) or XE0938_M.SEA (Macintosh). XE0938_W.EXE and XE0938_M.SEA are compressed, self-extracting files. After you download XE0938, run it to extract the file(s) it contains.
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: 4.00
Keywords : kbappnote kbprg
Version : WINDOWS:4.0,4.0a; MACINTOSH: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.