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:

Application.Dialogs(xlDialogFont).Show , 8

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.

0 0

Post a comment