Displaying Excel s Built-In Dialog Boxes


Displaying Excel's Built-In Dialog Boxes

Code that you write in VBA can execute many of Excel's Ribbon commands. And, if the command leads to a dialog box, your code can "make choices" in the dialog box (although the dialog box itself isn't displayed). For example, the following VBA statement is equivalent to choosing the Home image from book Editing image from book Find & Select image from book Go To command, specifying range A1:C3, and clicking OK. But the Go To dialog box never appears (which is what you want).

 Application.Goto Reference:=Range("A1:C3") 

In some cases, however, you may want to display one of Excel's built-in dialog boxes so that the end user can make the choices. You can do this by accessing the Dialogs collection of the Application object.

About the Dialogs collection

The Dialogs collection of the Application object consists of more than 200 members that represent most of Excel's built-in dialog boxes. Each has a predefined constant to make it easy to specify the dialog box that you need. For example, Excel's Go To dialog box is represented by the constant xlDialogFormulaGoto .

Use the Show method to actually display the dialog box. Here's an example that displays the Go To dialog box (see Figure 12-11):

image from book
Figure 12-11: This dialog box was displayed with a VBA statement.
 Application.Dialogs(xlDialogFormulaGoto).Show 

When the Go To dialog box is shown, the user can specify a named range or enter a cell address to go to. This dialog box is the one that appears when you choose Home image from book Editing image from book Find & Select image from book Go To (or press F5).

You can also write code to determine how the user dismissed the dialog box. Do this by using a variable. In the following statement, the Result variable will be True if the user clicks OK and False if the user clicks Cancel or presses Esc.

 Result = Application.Dialogs(xlDialogFormulaGoto).Show 
Note  

Contrary to what you might expect, the Result variable does not hold the range that was specified in the Go To dialog box.

The statement below displays the Open dialog box (equivalent to selecting Office image from book Open):

 Application.Dialogs(xlDialogOpen).Show 

Unfortunately, the Dialogs collection is poorly documented, and the newer dialog boxes aren't even available. The next section describes a better way to display built-in dialog boxes - for Excel 2007 only.

Executing Ribbon commands

In previous versions of Excel, programmers created custom menus and toolbars by using the CommandBar object. In Excel 2007, the CommandBar object is still available, but it doesn't work like it has in the past.

CROSS-REFERENCE  

Refer to Chapter 22 for more information about the CommandBar object.

The CommandBar object has also been enhanced in Excel 2007. You can use the CommandBar object to execute Ribbon commands using VBA. Many of the Ribbon commands display a dialog box. For example, the statement below displays the Go To dialog box:

 Application.CommandBars.ExecuteMso("GoTo") 

The ExecuteMso method accepts one argument, an idMso parameter that represents a Ribbon control. Unfortunately, these parameters are not listed in the Help system.

CD-ROM  

The companion CD-ROM contains a file, image from book  ribbon control names .xlsx , that lists all of the Excel Ribbon command parameter names.

Following is another example. This statement, when executed, displays the Font tab of the Format Cells dialog box (see Figure 12-12):

image from book
Figure 12-12: Using the ExecuteMso method to display a dialog box.
 Application.CommandBars.ExecuteMso("FormatCellsFontDialog") 
image from book
Executing an Old Menu Item Directly

Another technique to display a built-in dialog box requires knowledge of the pre-Excel 2007 toolbars (officially known as CommandBar objects). Although Excel 2007 no longer uses CommandBar objects, they are still supported for compatibility.

The following statement, for example, is equivalent to selecting the Go To menu item on the Edit menu:

 Application.CommandBars("Worksheet Menu Bar"). _    Controls("Edit").Controls("Go To...").Execute 

This statement, when executed, displays the Go To dialog box. Notice that the menu item captions must match exactly (including the ellipsis after Go To ).

Here's another example. This statement displays the Format Cells dialog box:

 Application.CommandBars("Worksheet Menu Bar"). _    Controls("Format").Controls("Cells...").Execute 

It's probably not a good idea to rely on CommandBar objects because they may be removed from a future version of Excel.

image from book
 



Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net