Microsoft Office Tutorials and References
In Depth Information
Prompting for a Directory
choosing the Home➜Editing➜Find & Select➜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 so by writing code that executes a Ribbon command.
Using the Dialogs collection of the Application object is another way to display an
Excel dialog box. However, Microsoft has not kept this feature up-to-date, so I don’t
even discuss it. The method I describe in this section is a much better solution.
In previous versions of Excel, programmers created custom menus and toolbars by using the
CommandBar object. In Excel 2007 and Excel 2010, the CommandBar object is still available, but
it doesn’t work like it has in the past.
Refer to Chapter 22 for more information about the CommandBar object.
The CommandBar object has also been enhanced, beginning with 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 following statement displays the Unhide dialog box (see Figure 12-10):
Application.CommandBars.ExecuteMso(“SheetUnhide”)
Figure 12-10: This dialog box was displayed with a VBA statement.
 
Search JabSto ::




Custom Search