Print Preview freezing Excel after BeforePrint VBA run

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top