Excel AppNote: "Using Dynamic Dialog Boxes" (XE0449)
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).
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:
- Type the following commands in cells C1 and C2:
=DIALOG.BOX(box)
=RETURN()
- Select cell C1 and choose Run from the Macro menu.
- 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."
--------------------------------------------------------------------
Additional query words:
4.00a
Keywords : kbappnote kbprg
Version : WINDOWS:3.0,4.0,4.0a; MACINTOSH:3.0,4.0
Platform : MACINTOSH WINDOWS
Issue type : kbinfo
|