Excel Dialog Boxes
Before you start creating your own forms, you should spend some time learning how to take advantage of dialog boxes that are built into Excel and are therefore ready for you to use. I'm not talking about your ability to manually select appropriate options but how to call these dialog boxes from your own VBA procedures.
Microsoft Excel has a special collection of built-in dialog boxes that are represented by constants beginning with xlDialog, such as xlDialogClear, xlDialogFont, xlDialogDefineName, and xlDialogOptionsView. These built-in dialog boxes are Microsoft Excel objects that belong to the built-in Dialogs collection. Each dialog object represents a built-in dialog box. Table 10-1: Frequently used built-in dialog boxes
Dialog Box Name
Constant
New xlDialogNew
Open xlDialogOpen
Save As xlDialogSaveAs
Page Setup xlDialogPageSetup
Print xlDialogPrint
Font xlDialogFont
To display a dialog box, use the Show method in the following format:
Application.Dialogs(consfanf).Show
For example, the following statement displays the Font dialog box:
Application.Dialogs(xlDialogFont).Show
The list of constants identifying Excel built-in dialog boxes is available in the Object Browser window after selecting the Excel library and searching for xlDialog (see Figure 10-1).
1. Open a new workbook and save it as Chap10.xls.
2. Switch to the Visual Basic Editor window.
Figure 10-1:
Constants prefixed with "xlDialog" identify Excel built-in dialog boxes.
3. Open the Immediate window.
4. Enter the following statements and see the results:
Application.Dialogs(xlDialogClear).Show
Application.Dialogs(xlDialogFont).Show
Application.Dialogs(xlDialogFontProperties).Show
Application.Dialogs(xlDialogDefineName).Show
Application.Dialogs(xlDialogOptionsView).Show
Figure 10-1:
Constants prefixed with "xlDialog" identify Excel built-in dialog boxes.
3. Open the Immediate window.
4. Enter the following statements and see the results:
Application.Dialogs(xlDialogClear).Show
Application.Dialogs(xlDialogFont).Show
Application.Dialogs(xlDialogFontProperties).Show
Application.Dialogs(xlDialogDefineName).Show
Application.Dialogs(xlDialogOptionsView).Show
The last instruction displays the Options dialog box View tab.
|
W Startup Task Par« Comments C ¡¿one |
[7 Formula bar W Status bar |
(7 Windows In Taskbar |
|
f* Comment incicator only |
Comment & indicator | |
|
¡¡Shwall |
C show Qlaceholders |
C Hide all |
|
F Page breads W Row & column headers F" Forrnutas P Outline symbols P" Grldllnes W Zero values Gricfines color : | Automatic ^J |
J* Horizontal scroll bar F Vertical scroll bar 17 sheet tabs | |
Figure 10-2:
Settings available on the Options dialog box View tab are identified by the xlDialogOptionsView constant.
After displaying a built-in dialog box, you can select an appropriate option, and Excel will format the selected cell or range, or the entire sheet. Although you can't modify the looks and behavior of a built-in dialog box, you can decide which initial setting the built-in dialog box will display when you show it from your VBA procedure. If you don't change the initial settings, VBA will display the dialog box with its default settings.
Suppose you want to display the Clear dialog box with the All option button selected. Normally, when Excel displays this dialog box, the Contents option button is selected. Enter the following statement in the Immediate window:
Application.DialogS(xlDialogClear).Show 1
You can include a list of arguments after the Show method. In the Clear dialog box, the All option button appears first in the group of four option buttons. Excel often numbers the available options. Therefore, All = 1, Formats = 2, Contents = 3, and Comments = 4. The built-in dialog box argument lists are available by searching the online help (see Figure 10-3).
To display the Font dialog box where the Arial 14-point font is already selected, try out the following instruction in the Immediate window:
Application.Dialogs(xlDialogFont).Show "Arial", 14
To specify only the font size, enter a comma in the position of the first argument:
|
Microsoft Visual Basic Help | ||||
|
\mm £ m if- | ||||
|
Contents Answer Wizard 1 [ndex ) |
Built-1 n Dialog Box Argument Lists J | |||
|
J What wotid you like to do? |
See Also | |||
|
built-in dialog |
Dialog box constant Argument list(s) | |||
|
1 Search | |
1 |
jdDiabgftctivate window_text, pane_num | ||
|
1 Select topic to dsplay: |
xJDjatogActiveCellFont for*, font_style, size, strtathrough, superscript, subscript, outline. | |||
|
Built-in dabo Box Arai/nerst Lists |
shadow, uriderlne, color, normal, background, start.char, char_count xJDiabgAddChartAutoformat name_text, descjext | |||
|
Diaiogs Property GetSaveAsFllerame Method | ||||
|
idDiabgAddnManager operation_num, addinname_text, copyjogical xlDiabgAlgnment horiz_aJign, wrap, vert_aign, crlentatlon, addjndent | ||||
|
xlDiabgApplyNames name_array, ignore, use_rowcol, ant „col, omltjow, ader_num, append Jast | ||||
|
xtuatogAfiplyStyle styie_text | ||||
|
Search on Web |
1 |
xfDubpMove x_num, y_num | ||
|
_ | ||||
Figure 10-3: Microsoft Excel built-in dialog box argument list
Figure 10-3: Microsoft Excel built-in dialog box argument list
The following instruction displays the Define Name dialog box, enters "John" in the Names in workbook text box, and places the reference to cell A1 in the Refers to box:
Application.Dialogs(xlDialogDefineName).Show "John", "=$A$1" The Show method returns True if you click OK and False if you cancel.
Post a comment