How to automatically change number of pages to print?

[Guest]

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?

 

[Mike Fogleman]

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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