views:

961

answers:

2

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
A: 

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
Robert Mearns
A: 

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

guitarthrower