Dialog Boxes in VBA

Location: http://www.mvps.org/dmcritchie/excel/dialog.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Specific Dialogs (#dialog)

  Sub Show_DialogPrint()
    Application.Dialogs(xlDialogPrint).Show
  End Sub

Finding dialogs in the Object Browser -- F2

Dialogs can be found under xlBuiltinDialog in the Object Browser.
You can find additional dialogs by right-clicking on the xlDialogPrint within the parentheses in the above macro and choosing either "List Properties/Methods" or "List Constants".

Navigation to a Sheetname (#MoreSheets)

Brings up list of sheetnames, this is the same list you get by right-clicking on a sheet navigation arrow button and then selecting "More Sheets...". You can place the following on a shortcut key combination. [ref. Chip Pearson & Dave Peterson] Also see thread for list of sheetnames in a listbox on a FORM.

Sub SheetList_CP()
  'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11 
  'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
  On Error Resume Next
  Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
  If Err.Number > 0 Then
    Err.Clear
    Application.CommandBars("Workbook Tabs").ShowPopup
  End If
  On Error GoTo 0
End Sub

More on Sheet navigation on my Toolbars and Build TOC pages.

Open File (#openfile)

This displays the open file dialog in XL. It does not open the file but you can assign the result to a string variable and use that to open the file. Nick Hodge, 2002-12-25.
myFile=Application.GetOpenFilename(Parameters here)
Workbooks.Open(myFile)
MS KB 213755 - XL2000: Macro to Change File Type to "All Files (*.*)" in Open Dialog Box
Sub Open_File()
    Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
End Sub

Printers (#printers)

application.Dialogs(xlDialogPrinterSetup).Show
Also see Related for more on printers, and how to find more dialogs (first topic) in Finding dialogs in the Object Browser -- F2.

Print Dialog box, using, a reply by Bill Manville, 1997/09/17
Printing with macros
Q166008 -- ACC: Enumerating Local and Network Printers

This page was introduced on February 8, 2002.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]