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.
******************************************


---
Regards,
Norman



"Husker87" <Husker87@discussions.microsoft.com> wrote in message
news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
>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.
>

[Dave Peterson]

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



Norman Jones wrote:
>
> 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.
> ******************************************
>
> ---
> Regards,
> Norman
>
> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> >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.
> >

--

Dave Peterson

[Norman Jones]

Hi Dave,

Thankyou!

Betwixt and between!

---
Regards,
Norman


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43886C07.CD074A56@verizonXSPAM.net...
> typo alert:
> change:
> Dim rnage As Range
> to
> Dim rng as range
>
>
>
> Norman Jones wrote:
>>
>> 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.
>> ******************************************
>>
>> ---
>> Regards,
>> Norman
>>
>> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
>> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
>> >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.
>> >
>
> --
>
> Dave Peterson

[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?


"Norman Jones" wrote:

> Hi Dave,
>
> Thankyou!
>
> Betwixt and between!
>
> ---
> Regards,
> Norman
>
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43886C07.CD074A56@verizonXSPAM.net...
> > typo alert:
> > change:
> > Dim rnage As Range
> > to
> > Dim rng as range
> >
> >
> >
> > Norman Jones wrote:
> >>
> >> 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.
> >> ******************************************
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> >> >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.
> >> >
> >
> > --
> >
> > Dave Peterson
>
>
>

[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

Husker87 wrote:
>
> 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?
>
> "Norman Jones" wrote:
>
> > Hi Dave,
> >
> > Thankyou!
> >
> > Betwixt and between!
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:43886C07.CD074A56@verizonXSPAM.net...
> > > typo alert:
> > > change:
> > > Dim rnage As Range
> > > to
> > > Dim rng as range
> > >
> > >
> > >
> > > Norman Jones wrote:
> > >>
> > >> 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.
> > >> ******************************************
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> > >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> > >> >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.
> > >> >
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
> >

--

Dave Peterson

[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" wrote:

> 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
>
> Husker87 wrote:
> >
> > 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?
> >
> > "Norman Jones" wrote:
> >
> > > Hi Dave,
> > >
> > > Thankyou!
> > >
> > > Betwixt and between!
> > >
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:43886C07.CD074A56@verizonXSPAM.net...
> > > > typo alert:
> > > > change:
> > > > Dim rnage As Range
> > > > to
> > > > Dim rng as range
> > > >
> > > >
> > > >
> > > > Norman Jones wrote:
> > > >>
> > > >> 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.
> > > >> ******************************************
> > > >>
> > > >> ---
> > > >> Regards,
> > > >> Norman
> > > >>
> > > >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> > > >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> > > >> >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.
> > > >> >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[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 wrote:
>
> 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" wrote:
>
> > 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
> >
> > Husker87 wrote:
> > >
> > > 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?
> > >
> > > "Norman Jones" wrote:
> > >
> > > > Hi Dave,
> > > >
> > > > Thankyou!
> > > >
> > > > Betwixt and between!
> > > >
> > > > ---
> > > > Regards,
> > > > Norman
> > > >
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:43886C07.CD074A56@verizonXSPAM.net...
> > > > > typo alert:
> > > > > change:
> > > > > Dim rnage As Range
> > > > > to
> > > > > Dim rng as range
> > > > >
> > > > >
> > > > >
> > > > > Norman Jones wrote:
> > > > >>
> > > > >> 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.
> > > > >> ******************************************
> > > > >>
> > > > >> ---
> > > > >> Regards,
> > > > >> Norman
> > > > >>
> > > > >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> > > > >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> > > > >> >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.
> > > > >> >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[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" wrote:

> 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 wrote:
> >
> > 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" wrote:
> >
> > > 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
> > >
> > > Husker87 wrote:
> > > >
> > > > 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?
> > > >
> > > > "Norman Jones" wrote:
> > > >
> > > > > Hi Dave,
> > > > >
> > > > > Thankyou!
> > > > >
> > > > > Betwixt and between!
> > > > >
> > > > > ---
> > > > > Regards,
> > > > > Norman
> > > > >
> > > > >
> > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > news:43886C07.CD074A56@verizonXSPAM.net...
> > > > > > typo alert:
> > > > > > change:
> > > > > > Dim rnage As Range
> > > > > > to
> > > > > > Dim rng as range
> > > > > >
> > > > > >
> > > > > >
> > > > > > Norman Jones wrote:
> > > > > >>
> > > > > >> 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.
> > > > > >> ******************************************
> > > > > >>
> > > > > >> ---
> > > > > >> Regards,
> > > > > >> Norman
> > > > > >>
> > > > > >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> > > > > >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> > > > > >> >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.
> > > > > >> >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[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.

Husker87 wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > Husker87 wrote:
> > > > >
> > > > > 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?
> > > > >
> > > > > "Norman Jones" wrote:
> > > > >
> > > > > > Hi Dave,
> > > > > >
> > > > > > Thankyou!
> > > > > >
> > > > > > Betwixt and between!
> > > > > >
> > > > > > ---
> > > > > > Regards,
> > > > > > Norman
> > > > > >
> > > > > >
> > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > news:43886C07.CD074A56@verizonXSPAM.net...
> > > > > > > typo alert:
> > > > > > > change:
> > > > > > > Dim rnage As Range
> > > > > > > to
> > > > > > > Dim rng as range
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Norman Jones wrote:
> > > > > > >>
> > > > > > >> 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.
> > > > > > >> ******************************************
> > > > > > >>
> > > > > > >> ---
> > > > > > >> Regards,
> > > > > > >> Norman
> > > > > > >>
> > > > > > >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> > > > > > >> news:A80FDAD6-23A6-442F-B1F1-2C5C5E43C19D@microsoft.com...
> > > > > > >> >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.
> > > > > > >> >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson