Get Control Before AND After Printing

[Guest]

I want to get control before printing, change the shading on certain cells so
that the shading does not show up on the printed page, and then get control
again after printing and change the shading back to what it was before
printing. The WorkbookBeforePrint event works fine and I get the desired
printing. But I don't know how to get control to change the shading back.

Is there some other event I can intercept that will let me make the
formatting change after printing?

If I use the .PrintOut method on a commnd button, I get control both before
and after printing. However, I lose the printer setup dialog (destination
printer, number of copies, etc.) that comes "for free" with the File/Print
command.

Is there something I can do to get the printer setup dialog to come up when
using .PrintOut?

TIA,

Phil

 

[Guest]

The whole thing is done with the before print. the general flow is to cancel
the print job, do your shading, disable the events print the document and
re-enable the events...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
'do your shading
ThisWorkbook.PrintOut
'undo your shading
Application.EnableEvents = True
End Sub