Print Preview freezing Excel after BeforePrint VBA run

[General Ledger]

Dear All,

I am having trouble with Print Preview in a file with a Workbook_BeforePrint event.

I have an Excel 2003 workbook that contains VBA code. In Private Sub Workbook_BeforePrint(Cancel As Boolean) I have code that if conditional formatting is turned on in a range of cells, then:

1. Cancel is True (the print job is stopped)
2. A message box is displayed informing the user the print job was cancelled

The only option in the mesasage box is "OK". After selecting OK, the user is free to continue doing whatever (save, close, edit, etc.).

The code works perfectly whether conditional formatting is or is not on in the examined range, whether the user selects the Print icon or Ctrl+P.

Every time the user selects the Print Preview icon, the message box is displayed, even if there is no conditional formatting turned on. The user can select OK as usual. Then Excel is frozen or locked. The Print Preview icon remains depressed or selected. The only thing that can be done is close Excel. I then get the normal "Do you want to save changes" dialog box.

I would like to understand:

1. Why does Print Preview always cause the BeforePrint event to cancel the event?
2. Why doesn't Excel return to a "normal status" (with the Print Preview icon no longer depressed) after running the module?

Happy New Year,

GL

Here is all the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

On Error Resume Next
If ActiveSheet.CodeName = "Sheet2" _
Or ActiveSheet.CodeName = "Sheet3" _
Or ActiveSheet.CodeName = "Sheet4" Then GoTo Done

Call Sort_Travel

Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Range("e4:e30,k4:m30,l1, d36, g36")
Cell.Select
x = Evaluate(Cell.FormatConditions(1).Formula1)
If x = True Then
Cancel = True
MsgBox "Key information is missing." & vbCrLf & vbCrLf & "See cells colored red.", vbCritical, " Printing stopped"
GoTo Done
End If
Next Cell

Done:

End Sub

 

[Norie]

I don't think Print Preview does cancel the event.

The first thing I would recommend you do with your code is remove On Error Resume Next.

I would also suggest you lose the Goto and Select.

The error part could just be hiding errors, the Goto is just not a good idea and the use of Select is rarely if ever needed.

In fact using Select can cause problems.