Set print area macro based on text entered in a cell

robertsmyth100

Board Regular
Joined
Sep 25, 2006
Messages
94
I have a sheet setup with 6 identical A4 pages on it spread horizontally. The title pains across the top and left hand side are frozen. The pages span from left to right and a user would fill in each page sequentially left to right. A user may only wish to use say 2 of these pages on the sheet, is there any way I can make Excel change the print area so that it only prints these two pages. Basically I would like to set it up so that if text is entered into a specific cell on a page then it will set the print area to print that page.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Let's say you have 60 rows and 20 columns per A4 sheet, starting from A1. I'm assuming the name of this sheet is Sheet1.

Code:
Sub MacroName()
     dim NumPages as Integer
     NumPages = InputBox("How many pages would you like to print?","Set Number of Pages","Please Enter a Number from 1-6")
     Worksheets("Sheet1").PageSetup.PrintArea = Worksheets("Sheet1").Range(0,0).Resize(NumPages * 20,60)
End Sub

Something like that.
 
Upvote 0
Thankyou!

I just wish to edit this macro slightly - Basically I want the print area to be set automatically based on whether text has been entered in a specific cell on that page. What I planned to do is to set up an IF statement in a cell that looks at whether text has been entered into the cell on each page and counts the pages. Then the macro looks at the page count and sets the print area accordingly. I can do the IF statement bit and I have edited the macro you gave me but I can't get it to work at the moment (I am a complete VB novice!!). Below is my macro, where cell D53 contains the number of pages.


Sub Set_print_area(ByVal Target As Range)
Dim NumPages As Range
NumPages = Range("D53")
Worksheets("1").PageSetup.PrintArea = Worksheets("1").Range(0, 0).Resize(NumPages * 21, 47)
End Sub
 
Upvote 0
Rich (BB code):
Sub Set_print_area(ByVal Target As Range)
     Dim NumPages As Range
     NumPages = Worksheets("1").Range("D53")
     Worksheets("1").PageSetup.PrintArea = Worksheets("1").Range(0, 0).Resize(NumPages * 21, 47)
End Sub
Maybe the part in red?

Does it give an error? Just not work? Where does the problem lie?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top