Show page setup window

[Chrissy]

Does anyone know how to get the page setup window to show in Excel VBA code? The people using the application want the ability to change the printer, and I can't figure out how to get the page setup window to show.

Thanks!

[RobDog888]

This should give you a starting point.

With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With

When ever you want to know how something is done, usually you can get the code from recording a macro.
Then in the VBA IDE you can look at the module of code that was generated to see what objects/methods/props
were used.

[Chrissy]

I don't want to set the parameters for them, I want the window to pop up and allow them to select the printer themselves.

[RobDog888]

Oh, Ok here it is.

Dim oDlg As Dialog
    Set oDlg = Application.Dialogs.Item(xlDialogPageSetup)
    With oDlg
        .Show
    End With

[Chrissy]

Perfect! Thank you!!

[D-niss]

Oh, Ok here it is.

Dim oDlg As Dialog
    Set oDlg = Application.Dialogs.Item(xlDialogPageSetup)
    With oDlg
        .Show
    End With

Very nice, I didn't know that!

[RobDog888]

Thanks, actually code like this is faster then the code generated by a recorded macro.
There is all kinds of things that you can do similar to this that you wouldnt even think you could do
Like this one, you can preset the dialog to your desired settings and execute it without the dialog
even being shown.

I have been thinking of writting a FAQ but I haven't had much free time this month.

[D-niss]

I would imagine!

that would be very usefull no doubt!
There must be a whole bunch of collections like you just used
that I'm not even aware of.