Workbook_BeforePrint > performs twice ?

[Peter]

Hi there,

I've put this code in the Workbook_BeforePrint event and when choosing a
printpreview I notice that when pressing escape, Excel shows the preview
again. Only after pressing the escape for the second time, Excel goes back
to normal view.

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
If ActiveSheet.Name = "Weekform" Then ActiveSheet.PageSetup.PrintArea =
_
Range("Weekform_PRINT").Address
If Range("User") = "" Then Range("User") = UserName 'Using JWalk's
function for retrieving logged-in user
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
End Sub

How do I prevent the second printpreview, and therefore also the second
printing of the sheet when not going for the preview ?

I've tried using "Cancel = True" right after the Private Sub
Workbook_BeforePrint(Cancel As Boolean) line.
Seems not to work

TIA,
Peter

 

[Dave Peterson]

You can add Cancel=true and stop the procedure from running normally (you're
controlling the printing).

But you can print sheets that aren't active. You may want to use something
like:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
With Worksheets("weekform")
.PageSetup.PrintArea = .Range("Weekform_PRINT").Address
If .Range("User") = "" Then
.Range("User") = UserName
'Using JWalk's function for retrieving logged-in user
End If
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
Cancel = True
End Sub

You're changing that .printarea each time you print, but so what???

 

[Terry Shannon]

I think it shows the print a second time because you are doing a
printout in the macro. The BeforePrint event gives you a chance to make
changes before the print job is sent to the printer. You can stop the
print job by setting Cancel to false. Since you do not set Cancel to
false you end up with two requests!

Terry

 

[Dave Peterson]

I think you'll actually want to set cancel = true.