Difference between print and print preview events in excel vba

Question

I have some code which intercepts the Before_Print event in excel to make sure that the user has filled in all the required fields before they print the sheet. However, I only want this code to fire when the user is actually printing, not when they are just calling the print preview.

Is there any way to tell in the Before_Print code whether the user is actually printing or just previewing?

The code that I currently have is (event stub was generated by excel):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Not Sheet2.CheckAllFieldsFilled Then
        Cancel = True
    End If
End Sub

Answer 1

I don't think there is a neat way to determine if the event is a print preview or print request.

The solution below is not particularly neat and inconveniences the user slightly, but it works.

The code cancels the event and then prompts the user, based on their response it displays the print preview or prints.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Print_or_Preview As XlYesNoGuess

Application.EnableEvents = False

    Cancel = True
    Print_or_Preview = MsgBox("Show Print Preview?", vbYesNo)

    If Print_or_Preview = True Then
        ActiveWindow.ActiveSheet.PrintPreview
        Else
        ActiveWindow.ActiveSheet.PrintOut
    End If
Application.EnableEvents = True

End Sub

Answer 2

To print you could do something like this:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

To Print Preview as was suggested:

ActiveWindow.ActiveSheet.PrintPreview 

Each one would would require a different button, but either way I would strongly suggest testing if you really need both, because the preview button may work for your print option, especially since you would in most cases be able to print straight from the preview.

I may be wrong here, but I don't think so.

Just a heads up, the print option I posted here will directly print, it won't request options, because they have been coded into the script, if you want to change how many copies you wish to print, change the copies:= to whatever number you wish...

Enjoy :)

Answer 3

I think I would provide a very visible button for the user to push when wanting to Print Preview.

Make the button hide for printing (in the options for the button), and have the code simply say:

ActiveWindow.ActiveSheet.PrintPreview