How to automatically change number of pages to print?

Here is the challenge… I have a worksheet that prints 3 pages. Depending on
how much information is put into it I have users that may only need to print
out 1 or 2 pages. I know you can manually go in and select to print only the
number of pages you want but I was wondering if you can, for example, have
them enter the number of pages they want to print in cell “A1” then click
print and the worksheet would only print that number of pages? Thanks. What
do you think?

In the code module for ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim prpg As Integer
prpg = Range("A1").Value
ActiveWindow.SelectedSheets.PrintOut To:=prpg
End Sub

Mike F

"Husker87" <Husker87@discussions.microsoft.com> wrote in message
news:A1B65A32-8598-4BE8-A9CE-192DCF5CF3CC@microsoft.com...
> Here is the challenge. I have a worksheet that prints 3 pages. Depending
> on
> how much information is put into it I have users that may only need to
> print
> out 1 or 2 pages. I know you can manually go in and select to print only
> the
> number of pages you want but I was wondering if you can, for example, have
> them enter the number of pages they want to print in cell "A1" then click
> print and the worksheet would only print that number of pages? Thanks.
> What
> do you think?

Thanks Mike for the reply. however when I pasted your code inot the
worksheet, I put a 1 in "A1" and its prints page one then prints all 3 (my
workbook has a print range of 1 wide by 3 tall) could that be the problem?

"Mike Fogleman" wrote:

> In the code module for ThisWorkbook:
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim prpg As Integer
> prpg = Range("A1").Value
> ActiveWindow.SelectedSheets.PrintOut To:=prpg
> End Sub
>
> Mike F
>
> "Husker87" <Husker87@discussions.microsoft.com> wrote in message
> news:A1B65A32-8598-4BE8-A9CE-192DCF5CF3CC@microsoft.com...
> > Here is the challenge. I have a worksheet that prints 3 pages. Depending
> > on
> > how much information is put into it I have users that may only need to
> > print
> > out 1 or 2 pages. I know you can manually go in and select to print only
> > the
> > number of pages you want but I was wondering if you can, for example, have
> > them enter the number of pages they want to print in cell "A1" then click
> > print and the worksheet would only print that number of pages? Thanks.
> > What
> > do you think?
>
>
>

Husker87:

> enter the number of pages they want to print in cell “A1”

if You want print 2,3 pages, cell “A1” is 2-3
if You want print only 2 pages, cell “A1” is 2

e.g. print 2 and 3 pages

Range("A1")="2-3"
Dim r As Variant
r = VBA.Split(Range("A1"), "-")
If UBound(r) > 0 Then
ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(1))
Else
ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(0))
End If



--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

> Here is the challenge… I have a worksheet that prints 3 pages. Depending on
> how much information is put into it I have users that may only need to print
> out 1 or 2 pages. I know you can manually go in and select to print only the
> number of pages you want but I was wondering if you can, for example, have
> them enter the number of pages they want to print in cell “A1” then click
> print and the worksheet would only print that number of pages? Thanks. What
> do you think?

Thanks for the reply... I pasted the code into the worksheet, entered the
number 1 in "A1" and it still prints all 3 pages when I click my print icon.
Could the fact that I have defined a print range with in the worksheet affect
it?

"chijanzen" wrote:

> Husker87:
>
> > enter the number of pages they want to print in cell “A1”
>
> if You want print 2,3 pages, cell “A1” is 2-3
> if You want print only 2 pages, cell “A1” is 2
>
> e.g. print 2 and 3 pages
>
> Range("A1")="2-3"
> Dim r As Variant
> r = VBA.Split(Range("A1"), "-")
> If UBound(r) > 0 Then
> ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(1))
> Else
> ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(0))
> End If
>
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "Husker87" wrote:
>
> > Here is the challenge… I have a worksheet that prints 3 pages. Depending on
> > how much information is put into it I have users that may only need to print
> > out 1 or 2 pages. I know you can manually go in and select to print only the
> > number of pages you want but I was wondering if you can, for example, have
> > them enter the number of pages they want to print in cell “A1” then click
> > print and the worksheet would only print that number of pages? Thanks. What
> > do you think?

Husker87:

pasted the code into the Thisworkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim r As Variant
r = VBA.Split(Range("A1"), "-")
If UBound(r) > 0 Then
ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(1))
Else
ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(0))
End If
End Sub

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

> Thanks for the reply... I pasted the code into the worksheet, entered the
> number 1 in "A1" and it still prints all 3 pages when I click my print icon.
> Could the fact that I have defined a print range with in the worksheet affect
> it?
>
> "chijanzen" wrote:
>
> > Husker87:
> >
> > > enter the number of pages they want to print in cell “A1”
> >
> > if You want print 2,3 pages, cell “A1” is 2-3
> > if You want print only 2 pages, cell “A1” is 2
> >
> > e.g. print 2 and 3 pages
> >
> > Range("A1")="2-3"
> > Dim r As Variant
> > r = VBA.Split(Range("A1"), "-")
> > If UBound(r) > 0 Then
> > ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(1))
> > Else
> > ActiveSheet.PrintOut From:=Val(r(0)), To:=Val(r(0))
> > End If
> >
> >
> >
> > --
> > 天行健，君�*�以自強不息
> > 地勢坤，君�*�以厚德載物
> >
> > http://www.vba.com.tw/plog/
> >
> >
> > "Husker87" wrote:
> >
> > > Here is the challenge… I have a worksheet that prints 3 pages. Depending on
> > > how much information is put into it I have users that may only need to print
> > > out 1 or 2 pages. I know you can manually go in and select to print only the
> > > number of pages you want but I was wondering if you can, for example, have
> > > them enter the number of pages they want to print in cell “A1” then click
> > > print and the worksheet would only print that number of pages? Thanks. What
> > > do you think?