Before Print Event or not? 1

[williekay]

I have an excel document that takes a long time to print. The solution is to open the document in page break view, print the document, and return to normal view. I do this usually with a Macro and the Macro works but this in the ThisWorkbook section does not???

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.View = xlNormalView
End Sub




Willie

 

[combo]

I doubt that in this case printing will be faster. Anyway, if you need do pick the 'BeforePrint' event, the logic is:
- cancel current printing action,
- disable events (to avoid recursive events),
- write your own print procedure,
- enable events:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Cancel = True
  Application.EnableEvents = False
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  Application.EnableEvents = True
End Sub

Please note that this event fires when the user previews workbook too. There is no need to change the view to xlPageBreakPreview.

combo