Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Hi JMay -

I'd suggest "flipping" the order of the commands that are issued to Excel.
Currently, you issue a Print request followed by a PrintPreview request. Try
starting with a PrintPreview request as follows:

Delete the Before_Print event code that controls printing. Put the
following line in a standard procedure in a VBA module and run it when you
want to preview the print job:

activesheet.printpreview <---change active sheet to whatever you want printed

Then, from the print preview dialog that appears, press the [Print...]
button if you want a hard copy or [Close] if you don't.

--
Jay


"JMay" wrote:

Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
Application.EnableEvents = False
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
Cancel = True
End With
Application.EnableEvents = True
End Sub



but I still don't understand why you don't just cut the code abd use the
button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Bob, thanks for your patience. I see your 2 adds to my code.
1)EnableEvents Off/On and 2) Cancel = True
This however creates another Q,
When I now click on the Toolbar button PRINT-PREVIEW
The Event (below) is Triggered,,

I thought all code was run in the order layed-out in a procedure.

If let's say the Application.EnableEvents = False WAS OMITTED
Wouldn't the code continue on thru the Set rng = Range("A5:C" & lrow)
Looks like the line Cancel = True is VOIDING anything and everything,
Even tough the .PrintPreview line preceeds it.

I'm not getting why the use of the EnableEvents, off and on;
Pretty confused at this point, Can you straighten me out?

TIA,

Jim

"Bob Phillips" wrote in message
:

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
Application.EnableEvents = False
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
Cancel = True
End With
Application.EnableEvents = True
End Sub



but I still don't understand why you don't just cut the code abd use the
button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Jim,

If you don't suppress events, the procedure is entered twice, once for the
original print/print preview, and then again when it meets the PrintPreview
in the code). Nothing seems to happen then.

The Cancel is cancelling the original print request as you want to force a
print preview.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks for your patience. I see your 2 adds to my code.
1)EnableEvents Off/On and 2) Cancel = True
This however creates another Q,
When I now click on the Toolbar button PRINT-PREVIEW
The Event (below) is Triggered,,

I thought all code was run in the order layed-out in a procedure.

If let's say the Application.EnableEvents = False WAS OMITTED
Wouldn't the code continue on thru the Set rng = Range("A5:C" & lrow)
Looks like the line Cancel = True is VOIDING anything and everything,
Even tough the .PrintPreview line preceeds it.

I'm not getting why the use of the EnableEvents, off and on;
Pretty confused at this point, Can you straighten me out?

TIA,

Jim

"Bob Phillips" wrote in message
:

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
Application.EnableEvents = False
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
Cancel = True
End With
Application.EnableEvents = True
End Sub



but I still don't understand why you don't just cut the code abd use the
button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview
screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Bob, Can the Cancel = True be moved form inside the With Activesheet
group to Outside it like so:
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
Cancel = True
Application.EnableEvents = True

Thanks,

Jim


"Bob Phillips" wrote in message
:

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
Application.EnableEvents = False
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
Cancel = True
End With
Application.EnableEvents = True
End Sub



but I still don't understand why you don't just cut the code abd use the
button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

That should be fine, as it is not a method of the activesheet, but an
argument of the procedure.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, Can the Cancel = True be moved form inside the With Activesheet
group to Outside it like so:
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
Cancel = True
Application.EnableEvents = True

Thanks,

Jim


"Bob Phillips" wrote in message
:

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
Application.EnableEvents = False
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
Cancel = True
End With
Application.EnableEvents = True
End Sub



but I still don't understand why you don't just cut the code abd use the
button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview
screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,

Also...
I am triggering this Before_Print event by clicking on the Toolbar
Button PRINT-PREVIEW (if that matters).
Jim


"JMay" wrote in message
:

Bob, thanks -- but here is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lrow As Long
Dim rng As Range
lrow = Range("B65536").End(xlUp).Row
Set rng = Range("A5:C" & lrow)
With ActiveSheet
.PageSetup.PrintArea = rng.Address
.PrintPreview
End With
End Sub

It seems that I need an additional statement above, because when
I click on the Close button which appears on the Preview Screen
My Printer takes-off printing the 40 (unwanted) sheets, grrrrrr..




"Bob Phillips" wrote in message
:

Why not just use the Print Preview toolbar button?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMay" wrote in message
...
Often I only want to Print Preview a Doc
What is necessary in the Before_Print Code
So that when I click on the Close button in the Print-Preview screen.
I am returned to my worksheet INSTEAD of the printer cranking
Up and beginning to print my umpteen pages UNNECESSARILY.

Thanks,