Another way to suppress print besides Cancel = True?

mikeburg

Board Regular
Joined
Jun 23, 2005
Messages
165
I have the following code to execute BeforePrint event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Print Envelopes" Then
Application.OnTime Now + TimeValue("00:00:05"), ThisWorkbook.Name & "!PrintEnvelopes"
Cancel = True
End If
End Sub

The problem is that I have a print routine within the code to be executed 5 seconds after clicking & the Cancel = True prevents it.

Is there another way to suppress printing without using Cancel = True in the BeforePrint event?

Or is there a way to send the initial output to a dummy printer or something that really does not print?

I want to be able to print via clicking printer Icon or running the code.

Thanks a million. mikeburg
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I have NOT tested this, but hopefully it will work OK :-
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Print Envelopes" Then
    Cancel = True
    Application.EnableEvents = False
    Application.OnTime Now + TimeValue("00:00:05"), ThisWorkbook.Name & "!PrintEnvelopes"
    Application.EnableEvents = True
    End If
End Sub
 

mikeburg

Board Regular
Joined
Jun 23, 2005
Messages
165
Thanks so very much for the suggestion, but nothing will print.

Any other ideas? mikeburg
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
You need to put a breakpoint in the code (Debug/toggle Breakpoint). Click print button. Then use F8 key to step through the code line by line to see what is happening.

NB. Have a macro with just the line :-
Code:
Appplication.EnableEvents = True

and run it if necessary (or at any other time) ......

because if the code stops in the middle without running this line in the routine the Before_Print event (and any others) will not run.
It is there to stop the repetition that you mention. We cancel the first print and do not want the Event itself to run when your other subroutine prints.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,448
Members
412,595
Latest member
slim313
Top