Executing a menu item directly

The second technique to display a built-in dialog box requires some knowledge of toolbars (officially known as CommandBar objects). For now, be aware that you can execute a menu item. And you can take advantage of the fact that selecting a menu item displays a dialog box.

I cover CommandBars extensively in Chapters 22 and 23.

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").

Unlike using the Dialogs collection, this technique does not allow you to specify default values for the dialog boxes.

The examples in this section use language-specific references to the CommandBar controls. Consequently, these statements will work only in English language versions of Excel. For applications that will be used with other language versions of Excel,you can use the FindControl method, along with the Id property for the command. See Chapter 22 for more information.

In the previous section, I point out a problem with accessing the Dialogs collection: It's not possible to display a tabbed dialog box. That problem doesn't exist when you execute a menu command. The following statement, for example, displays the Format Cells dialog box (with all its tabs):

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

By the way, the Execute method also works with toolbar controls that don't display a dialog box. The following statement, for example, is equivalent to clicking the Bold button on the Formatting toolbar:

Application.CommandBars("Formatting").Controls("Bold").Execute

I can't think of any reason to do this, however. A more efficient way to toggle the Bold property of the selected cells is to use a statement like this:

Selection.Font.Bold = Not Selection.Font.Bold

Chapter 13

Was this article helpful?

0 0

Post a comment