excel 2007 vba Application.Dialogs(xlDialogPrint).Show crashes if user chooses print preview

Question

In Excel 2007, have a button that triggers a macro, which selects a few sheets out of many and sends to Application.Dialogs(xlDialogPrint).Show. As part of selecting the few sheets there are other macros triggered like showing certain rows, password protecting and unprotect-ing, etc.

It prints and cancels fine, except when users click the 'print preview' button in that printer dialog box. It shows the printer dialog fine, but no matter if they hit print or close it crashes.

It appears to run through the whole macro a second time and crashes because expected values and settings are not in place like normal when it runs through the first time.

Any way to account or or capture the print preview dialog stuff when print preview is launched via the printer dialog from Application.Dialogs(xlDialogPrint).Show?

I have tried changing passed parameters like

Application.Dialogs(xlDialogPrint).Show ,,,,,False
Application.Dialogs(xlDialogPrint).Show Arg6:=False

But these are not working; I've read that you can't alter the dialog anyway.

(Hope this is clear)

Answer 1

Not sure if you want to show print preview directly from the button click or not. Usually I use something like this. I find it easier to view the preview first, then decide if I want to print out a hard copy. But it might not work for your situation.

Private Sub CommandButton1_Click()

    Dim vSheets() As Variant

    vSheets = Array("Sheet1", "Sheet2")
    ActiveWorkbook.Sheets(vSheets).Select 'sheets need to be selected
    ActiveWorkbook.PrintOut preview:=True 'brings up print preview
End Sub