How do I pause Macro until either print or back is selected in Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")

Question

I have a user form that selects sheets to print and then prints them once a command button is clicked.

I want to open the "Backstage" PrintPreviewandPrint page to allow for simultaneous selection of printer properties and preview the selected sheet for printing. Once I have clicked print, or the back arrow, I want to PrintPreviewandPrint page to close and select "Home" screen.

When I use

Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")

the desired screen is displayed, but the macro continues to run and the print preview show the home screen. i.e. the macro does not wait for an input from the PrintPreviewandPrint page before continuing.

Code is

Private Sub PrintDoc()

Sheets("Doc").Select
Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
Sheets("Home").Select
End Sub

Any help will be much appreciated

Answer 1

I found a slightly messy solution...

First I call the print preview with:

Sub Printdoc()

    Sheets("Doc").PrintPreview
    Sheets("Home").Select

End Sub

Then I call the Print Dialogue if print is selected

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Application.Dialogs(xlDialogPrint).Show

End Sub

Either way(Print or Cancel), the PrintDoc macro completes by displaying the "Home" screen

Answer 2

• Remove the Sheets("Home").Select

• Add a routine in the module containing your PrintDoc:

  Sub AfterPrint()
      Sheets("Home").Select
  End Sub
  

• put this in your ThisWorkbook module

  Private Sub Workbook_BeforePrint(Cancel As Boolean)
      Application.OnTime Now, "AfterPrint"
  End Sub