ID: Q111350
The information in this article applies to:
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.
--------------------------------------------------------------------
======================================================================
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. |
-----------------------------------------------------------------------
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.
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.
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.
1. In Windows, press ALT+SPACEBAR to display the Control menu.
2. From the Control menu, choose Run.
3. 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.
<graphic deleted> Figure 1 - Default dialog box in the Dialog Editor
1. Select the Item menu to display a list of available items.
2. 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 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.
For more information about working with items in a dialog box, see pages 264-266 of the "User's Guide 2."
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
1. In the Dialog Editor, choose Text from the Item menu.
2. When the text box appears, type NAME: (do not press ENTER).
3. From the Item menu, choose Edit Box, and select the Text option.
Choose OK.
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.
1. From the Item menu, choose Text.
2. When the text box appears, type CLASSIFICATION: (do not press
ENTER).
Choose OK.
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:
1. From the Item menu, choose Group.
2. When the group box appears, type CLASS CHOICES: (do not
press ENTER).
4. When the Option button appears, type CALCULUS (do not press ENTER).
5. 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."
1. From the item menu, choose Button. From the group of options,
select the OK option and then choose the OK button.
instead of OK).
Once you have created the dialog box in the Dialog Editor, you need to copy it to a macro sheet.
1. In the Dialog Box editor, choose Select Dialog from the Edit menu.
2. From the Edit menu, choose Copy.
3. 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.
Macro Sheet option. Choose OK.
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).
7. 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.
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.
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).
| 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
----------------------------------------------------------------------
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."
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."
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
-------------------------------------------------------------
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, 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
------------------------------------------------------------------------
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
----------------------------------------------------------
1. Define the macro by selecting cell A1 (which contains the
Registration_Dialog_Box macro).
select Command, and then choose OK.
macro, and then choose OK.
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
----------------------------------------------------------
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).
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
-----------------------------------------------------------------------
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
Keywords : kbappnote kbprg
Version : WINDOWS:4.0,4.0a; MACINTOSH:4.0
Platform : MACINTOSH WINDOWS
Issue type : kbinfoLast Reviewed: September 2, 1998