Excel AppNote: "Introduction to Custom Dialog Boxes" (XE0938)
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.
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(C)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
Additional query words:
Keywords : kbappnote kbprg
Version : MACINTOSH:4.0; WINDOWS:4.0,4.0a
Platform : MACINTOSH WINDOWS
Issue type : kbinfo
|