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