How to show print dialog box and print preview on same screen?

Question

I am trying to emulate Ctrl-P in Excel 2013 where the print dialog box is shown on the left with the print preview on the right.

(Although where the preview displays, I always have to click "Show Print Preview" first. I can't find a way to force the preview to show every time).

I tried the following:

Application.Dialogs(xlDialogPrint).Show

This shows the old style dialog box where you need to click the "Preview" button

ActiveSheet.PrintPreview

This shows the preview but doesn't allow the printer to be changed from the same screen.

Majesty

Answer 1

Something like this?

Excel

Option Explicit
Public Sub Example()
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
End Sub

CommandBars.ExecuteMso Method (MSDN) is useful method in cases where there is no object model for a particular command.

For Outlook

Option Explicit
Public Sub Example()
    Dim Inspector As Outlook.Inspector
    Set Inspector = Application.ActiveInspector

    If Not Inspector Is Nothing Then
        Dim cmd As Office.CommandBars
        Set cmd = Inspector.CommandBars

        cmd.ExecuteMso ("FilePrintPreview")
    Else
        ActiveExplorer.selection(1).Display
        Set cmd = ActiveInspector.CommandBars
        cmd.ExecuteMso ("FilePrintPreview")
    End If
End Sub

Cowgirl

Yes thanks! I am surprised I couldn't find that anywhere, would have thought more people would have wanted to do the same. – Majesty

No need for parentheses around the text in quotes, so use Application.CommandBars.ExecuteMso "PrintPreviewAndPrint", despite what the macro recorder and the online documentation show. – Presbyter