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" <(E-Mail Removed)> wrote in message
news:c1u5h.34685$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:#U1a$#(E-Mail Removed):

> Why not just use the Print Preview toolbar button?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "JMay" <(E-Mail Removed)> wrote in message
> news:c1u5h.34685$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:3rF5h.32505$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#U1a$#(E-Mail Removed):
>
> > Why not just use the Print Preview toolbar button?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "JMay" <(E-Mail Removed)> wrote in message
> > news:c1u5h.34685$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:3rF5h.32505$(E-Mail Removed):

> 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" <(E-Mail Removed)> wrote in message
> news:#U1a$#(E-Mail Removed):
>
> > Why not just use the Print Preview toolbar button?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "JMay" <(E-Mail Removed)> wrote in message
> > news:c1u5h.34685$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed):

> 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" <(E-Mail Removed)> wrote in message
> news:3rF5h.32505$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:#U1a$#(E-Mail Removed):
> >
> > > Why not just use the Print Preview toolbar button?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "JMay" <(E-Mail Removed)> wrote in message
> > > news:c1u5h.34685$(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed):

> 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" <(E-Mail Removed)> wrote in message
> news:3rF5h.32505$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:#U1a$#(E-Mail Removed):
> >
> > > Why not just use the Print Preview toolbar button?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "JMay" <(E-Mail Removed)> wrote in message
> > > news:c1u5h.34685$(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
news:OjG5h.32821$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed):
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:3rF5h.32505$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:#U1a$#(E-Mail Removed):
> > >
> > > > Why not just use the Print Preview toolbar button?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "JMay" <(E-Mail Removed)> wrote in message
> > > > news:c1u5h.34685$(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
news:uAG5h.33385$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed):
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:3rF5h.32505$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:#U1a$#(E-Mail Removed):
> > >
> > > > Why not just use the Print Preview toolbar button?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "JMay" <(E-Mail Removed)> wrote in message
> > > > news:c1u5h.34685$(E-Mail Removed)...
> > > > > 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,
> > > > >
> > > > >
> > >
>