Workbook_BeforePrint(Cancel As Boolean) ...how about AfterPr

bill · Mar 8th, 2002, 11:17 AM

Would like to capture a AFTERPRINT event in EXCEL 97.

Thank you.

Damon Ostrander · Mar 8th, 2002, 12:24 PM

Hi Bill,

There is no AfterPrint event, but you can effectively create your own using the BeforePrint event as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Static PrintRequest As Boolean

If PrintRequest = True Then Exit Sub

PrintRequest = True
ActiveSheet.PrintOut

'PUT YOUR AfterPrint CODE HERE
'This code will execute AFTER the print is completed

PrintRequest = False
Cancel = True
End Sub

The way this works is simply to cancel the manually-requested print, and instead execute a print from VBA so that right after the print you can do whatever operation you want.

Keep Excelling.

Damon

Barrie Davidson · Mar 8th, 2002, 12:30 PM

Damon, what a great idea! I've encountered this before and never even thought of going that route.

Regards,

Ivan F Moala · Mar 8th, 2002, 08:28 PM

Damon

Good idea.....however it's the Printpreview
that usually is the problem for Print event
codes....ie. code will also run in Printpreview........I'm trying to work on
code around this...so far I only have a hack

cheers

Ivan

bill · Mar 11th, 2002, 07:43 AM

Damon,

That was an excellent Idea.

Not too sure about IVAN and the problem he faces using PRINT PREVIEW...

4 now though, the solution is what the doctor ordered!
Bill

Ivan F Moala · Mar 24th, 2002, 03:28 AM

Quote:

On 2002-03-11 06:43, bill wrote:
Damon,

That was an excellent Idea.

Not too sure about IVAN and the problem he faces using PRINT PREVIEW...

4 now though, the solution is what the doctor ordered!
Bill

Hi Bill...the problem is when you do a printpreview the "AFTER print event" will
still run...try it and see....
This is what I'm trying to work over...
at the moment I just have a Hack fix...

Ivan

bill · Mar 26th, 2002, 08:25 AM

Thanks Ivan,

I actually noticed the flaw when an associate dropped a stack of printouts on my desk due to print preview

No fix yet I presume?

Mar 8th, 2002, 12:24 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Bill, There is no AfterPrint event, but you can effectively create your own using the BeforePrint event as follows: Private Sub Workbook_BeforePrint(Cancel As Boolean) Static PrintRequest As Boolean If PrintRequest = True Then Exit Sub PrintRequest = True ActiveSheet.PrintOut 'PUT YOUR AfterPrint CODE HERE 'This code will execute AFTER the print is completed PrintRequest = False Cancel = True End Sub The way this works is simply to cancel the manually-requested print, and instead execute a print from VBA so that right after the print you can do whatever operation you want. Keep Excelling. Damon __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Mar 8th, 2002, 12:30 PM	#3
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	Damon, what a great idea! I've encountered this before and never even thought of going that route. Regards, __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 11:17 AM	#1
bill Board Regular Join Date: Mar 2002 Location: Houston TX Posts: 549	Would like to capture a AFTERPRINT event in EXCEL 97. Thank you.

Mar 8th, 2002, 08:28 PM	#4
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	Damon Good idea.....however it's the Printpreview that usually is the problem for Print event codes....ie. code will also run in Printpreview........I'm trying to work on code around this...so far I only have a hack cheers Ivan

Mar 11th, 2002, 07:43 AM	#5
bill Board Regular Join Date: Mar 2002 Location: Houston TX Posts: 549	Damon, That was an excellent Idea. Not too sure about IVAN and the problem he faces using PRINT PREVIEW... 4 now though, the solution is what the doctor ordered! Bill

Mar 26th, 2002, 08:25 AM	#7
bill Board Regular Join Date: Mar 2002 Location: Houston TX Posts: 549	Thanks Ivan, I actually noticed the flaw when an associate dropped a stack of printouts on my desk due to print preview No fix yet I presume?