Can I automatically select the number of pages to print?

[Husker87]

I have a 3 page form that users fill out. I have set up a formula to return
the number of pages in cell “A1†that will need to be printed (1, 2, or 3)
depending on how much of the form the user has completed. Instead of having
the user specify how many pages to print in page setup, I would like Excel to
automatically print 1, 2, or all 3 pages based on what number is in cell “A1â€
when the user simple clicks on the print icon. Is this possible? I have
tried suggestions (macro in Thisworksheet) that prints the selected page then
all three. I’m still looking for a solution.

Put another way… if “A1†has a “1â€, print the first page. If “A1†has a “2â€
in it… print pages one and two, and if it has a “3†in it, print all three
pages. Thanks for reading this.

 

[Norman Jones]

Hi Husker87,

Try:

'=================>>
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rnage As Range

Set rng = ThisWorkbook.Sheets("Sheet1"). _
Range("A1") '<<==== CHANGE

On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveWindow.SelectedSheets.PrintOut , _
From:=1, _
To:=rng.Value

XIT:
Application.EnableEvents = True
End Sub
'<<=================


This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module):

******************************************
Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
******************************************

 

[Dave Peterson]

typo alert:
change:
Dim rnage As Range
to
Dim rng as range

 

[Norman Jones]

Hi Dave,

Thankyou!

Betwixt and between!

 

[Husker87]

Thanks Norman and Dave. Small problem however....
This is what I typed:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

Set rng = ThisWorkbook.Sheets("Special Copy of Schedule"). _
Range("M2") '<<==== CHANGE

On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveWindow.SelectedSheets.PrintOut , _
From:=1, _
To:=rng.Value

XIT:
Application.EnableEvents = True
End Sub


The name of the worksheet is, "Special Copy of Schedule" and the cell which
has the number of pages is "M3". When I run it I get an error on line
"From:=1, _" It says ... Compile Error: Named argument already specified.
It highlights the number 1 from that line... what do you think I am doing
wrong?

Hi Dave,

Thankyou!

Betwixt and between!

 

[Dave Peterson]

If you want to use M3, then change that M2 to M3 (on the '<<==== CHANGE line).

And get rid of the comma after .printout:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

Set rng = ThisWorkbook.Sheets("Special Copy of Schedule"). _
Range("M3") '<<==== CHANGE

On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveWindow.SelectedSheets.PrintOut from:=1, to:=rng.Value

XIT:
Application.EnableEvents = True
End Sub

Thanks Norman and Dave. Small problem however....
This is what I typed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

Set rng = ThisWorkbook.Sheets("Special Copy of Schedule"). _
Range("M2") '<<==== CHANGE

On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveWindow.SelectedSheets.PrintOut , _
From:=1, _
To:=rng.Value

XIT:
Application.EnableEvents = True
End Sub

The name of the worksheet is, "Special Copy of Schedule" and the cell which
has the number of pages is "M3". When I run it I get an error on line
"From:=1, _" It says ... Compile Error: Named argument already specified.
It highlights the number 1 from that line... what do you think I am doing
wrong?

 

[Husker87]

Dave... Many Thanks... it worked. It does however limit each worksheet in
the workbook to only print the number of pages specified on the worksheet
"Special Copy of Schedule".

Do you know if you can you put a macro in each worksheet rather than in
Thisworkbook that would reference a cell on that worksheet to dictate how
many pages of that worksheet to print? I have tried but can't seem to get
the syntax correct.

Bill

 

[Dave Peterson]

The bad news is that you can print too many ways <bg>.

Once you get to the File|print dialog, you can print selection, whole workbook,
or activesheets.

If you're always printing just the activesheet, you can use code like Norman
suggested.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

Set rng = ActiveSheet.Range("M3") '<<==== CHANGE

On Error GoTo XIT

Application.EnableEvents = False
Cancel = True
ActiveSheet.PrintOut from:=1, to:=rng.Value

XIT:
Application.EnableEvents = True
End Sub

If the cell that holds the pages to print can vary location, I think I'd define
a worsheet name (not a global name) to that cell.

Select the first worksheet
Select the cell
insert|Name|Define
Names in workbook box: Sheet1!ToPage
(The refers to box should match the selected cell)

In that Sheet1!ToPage string, change Sheet1 to the worksheet name like:
'Special Copy of Schedule'!ToPage
(surrounded by apostrophes if you need them!)

Then the code becomes:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range

On Error GoTo XIT

Set rng = ActiveSheet.Range("topage")

Application.EnableEvents = False
Cancel = True
ActiveSheet.PrintOut from:=1, to:=rng.Value

XIT:
Application.EnableEvents = True
End Sub

===========
But there are lots of times, I don't want an event macro messing up my
choices--special requirements and the like.

I think I'd be more likely to make a dedicated macro that prints the activesheet
(and just that activesheet). It'll look almost the same as the code that Norman
gave you, but it'll be placed into a general module.

Option Explicit
Sub PrintActiveSheet()
Dim rng As Range

On Error GoTo XIT
Set rng = ActiveSheet.Range("topage")
ActiveSheet.PrintOut preview:=True, from:=1, to:=rng.Value
Exit Sub

XIT:
MsgBox "Print failed! Contact Husker Bill ASAP"

End Sub


I'd drop a button from the Forms toolbar on each sheet that needed this kind of
behavior. And assign this one macro to each of those buttons--and remove
Norman's event code from the ThisWorkbook module.

ps. I added "preview:=true" to save some trees while testing.

 

[Husker87]

Dave,
Great job – Thank you! This was the code I was looking for.

Set rng = ActiveSheet.Range("M3")

One last question… What’s one of the best books you have come across that I
can learn VBA code from? Any suggestions?

Again, Thanks for your help.

Bill

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Depending on how advanced you are...

Professional Excel Development
By Stephen Bullen, Rob Bovey, John Green

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Dave,
Great job – Thank you! This was the code I was looking for.

Set rng = ActiveSheet.Range("M3")

One last question… What’s one of the best books you have come across that I
can learn VBA code from? Any suggestions?

Again, Thanks for your help.

Bill