Help with Modification of code

[JMay]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Selection.Font.ColorIndex = 2
ActiveSheet.PrintPreview
Selection.Font.ColorIndex = 1
End Sub

When I select certain cells then click on the Print-Preview Icon
The selected cells are in affect hidden;
I don't want to actually Print so I click on Close (Print-Preview Screen).
It closes, but it also print-out my sheet (which I don't want it to do)
grrrrrrr..
What's missing from my code?
TIA..

 

[JE McGimpsey]

Try:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Selection.Font.ColorIndex = 2
ActiveSheet.PrintPreview
Selection.Font.ColorIndex = 1
Cancel = True
Application.EnableEvents = True
End Sub

 

[JMay]

Much appreciated JE,

hummmmm, it's one of those "trigger-looping" events. I need to understand
these things better (than I now do).

Bear with me, but to better understand how and why these things work this
way
let me run through my original code just to see the need for the disabling
of the event.
Excel interprets (my original code) as follows:
1) by pressing the Print-Preview Icon I (Excel) will qualify that action
also and with the same weight as your pressing the Print Icon (or File Print
on the menu). and no matter what code that follows I will Print (hardcopy)
of whatever is out there (whether range assigned by you the user or my best
guess of what I think you want printed).

2)Ok, the first thing I'm to do is change the font color (of what's
selected) to White. Done

3)Now, I'm to Preview what will print, if and when you print. Done

4)Even though you close the Print-Preview Window my next (and last) task is
to
change the font color (of what's selected) to Black.

5) I'm now going to Print the document. ' since you have used a Before
Print event (and even though it was a Print-Preview Icon that you pressed -
it's the same to me as your saying Print!!!

Am I right so far? (before continuing?)
Thanks,

 

[Tom Ogilvy]

Both print and print preview trigger the beforeprint event regardless of how
they were initiated.

There is nothing passed to that event that discriminates which action is to
follow (print or print preview).


So issuing a non-conditional print preview within the beforeprint event will
initiate another beforeprint event and so on and so on.

 

[Tom Ogilvy]

I would try code like this for what you describe you want to do (cancel the
print if the user presses close in print preview)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Selection.Font.ColorIndex = 2
if ActiveSheet.PrintPreview then
ActiveSheet.Pintout
End if
Cancel = True
Selection.Font.ColorIndex = 1
Application.EnableEvents = True
End Sub

 

[JMay]

Thanks Tom for both explanations. It helps a great deal.
Jim May