Setting print area depending on a cell value
 

I am very new to VBA programming; in fact it still hurts when I read some of
the code that is posted!
I have a worksheet (called €œForm99€�) that is populated by data entries
placed on another worksheet (called €œData Entry€�).
On Form99 I have cell N2 that is loaded through a nested IF statements
(=IF('Data Entry'!$A$9="","",IF('Data Entry'!$A$26="","1",IF('Data
Entry'!$A$43="","2",IF('Data Entry'!$A$60="","3","4")))))
I want to insert a button that runs a print macro to the default printer;
the print area depending on the value in cell N2. Specifically,
if N2 <1 print nothing (no print area selected message)
if N2 = 1 print area is A1:Q36
if N2 = 2 print area is A1:Q72
if N2 = 3 print area is A1:Q108
if N2 = 4 print area is A1:Q144
Then reset print area to nothing
Thank you in advance for your help

--
Rick

Setting print area depending on a cell value
 

hi,
sub macsetprintarea
Range("A1").select
If Range("N2").value <1 then
ActiveSheet.PageSetup.PrintArea = ""
else
if Range("N2").value =1 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$36"
else
if Range("N2").value = 2 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$72"
else
if Range("N2").value = 3 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$108"
else
if Range("N2").value = 4 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$144"
end if
end if
end if
end if
end if
end sub

-----Original Message-----
I am very new to VBA programming; in fact it still hurts
when I read some of
the code that is posted!
I have a worksheet (called ?oForm99?�) that is
populated by data entries
placed on another worksheet (called ?oData Entry?�).
On Form99 I have cell N2 that is loaded through a nested
IF statements
(=IF('Data Entry'!$A$9="","",IF('Data Entry'!
$A$26="","1",IF('Data
Entry'!$A$43="","2",IF('Data Entry'!$A$60="","3","4")))))
I want to insert a button that runs a print macro to the
default printer;
the print area depending on the value in cell N2.
Specifically,
if N2 <1 print nothing (no print area selected message)
if N2 = 1 print area is A1:Q36
if N2 = 2 print area is A1:Q72
if N2 = 3 print area is A1:Q108
if N2 = 4 print area is A1:Q144
Then reset print area to nothing
Thank you in advance for your help

--
Rick
.

Setting print area depending on a cell value
 

it can be done without VBA..

define names
Sheet1!Print_Area =Sheet1!Print_Formula
Sheet1!Print_Formula =INDIRECT(CHOOSE(Sheet1!$N$2;"a1:q36";"a1:q72"))

I've used the construction with double names
so it's easy to repair when a user accidently
sets the printArea via the menu.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


wrote:

hi,
sub macsetprintarea
Range("A1").select
If Range("N2").value <1 then
ActiveSheet.PageSetup.PrintArea = ""
else
if Range("N2").value =1 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$36"
else
if Range("N2").value = 2 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$72"
else
if Range("N2").value = 3 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$108"
else
if Range("N2").value = 4 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$144"
end if
end if
end if
end if
end if
end sub

-----Original Message-----
I am very new to VBA programming; in fact it still hurts when I read
some of the code that is posted!
I have a worksheet (called ?oForm99?�) that is populated by data
entries placed on another worksheet (called ?oData Entry?�).
On Form99 I have cell N2 that is loaded through a nested IF
statements
(=IF('Data Entry'!$A$9="","",IF('Data Entry'! $A$26="","1",IF('Data
Entry'!$A$43="","2",IF('Data Entry'!$A$60="","3","4"))))) I want to
insert a button that runs a print macro to the default printer; the
print area depending on the value in cell N2. Specifically, if N2
<1
print nothing (no print area selected message) if N2 = 1 print area is
A1:Q36 if N2 = 2 print area is A1:Q72
if N2 = 3 print area is A1:Q108
if N2 = 4 print area is A1:Q144
Then reset print area to nothing
Thank you in advance for your help

--
Rick
.

Setting print area depending on a cell value
 

I got it to work, problem was with merged cells. Thanks for the help.

Rick

" wrote:

hi,
sub macsetprintarea
Range("A1").select
If Range("N2").value <1 then
ActiveSheet.PageSetup.PrintArea = ""
else
if Range("N2").value =1 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$36"
else
if Range("N2").value = 2 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$72"
else
if Range("N2").value = 3 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$108"
else
if Range("N2").value = 4 then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$144"
end if
end if
end if
end if
end if
end sub

-----Original Message-----
I am very new to VBA programming; in fact it still hurts
when I read some of
the code that is posted!
I have a worksheet (called â?oForm99â?�) that is
populated by data entries
placed on another worksheet (called â?oData Entryâ?�).
On Form99 I have cell N2 that is loaded through a nested
IF statements
(=IF('Data Entry'!$A$9="","",IF('Data Entry'!
$A$26="","1",IF('Data
Entry'!$A$43="","2",IF('Data Entry'!$A$60="","3","4")))))
I want to insert a button that runs a print macro to the
default printer;
the print area depending on the value in cell N2.
Specifically,
if N2 <1 print nothing (no print area selected message)
if N2 = 1 print area is A1:Q36
if N2 = 2 print area is A1:Q72
if N2 = 3 print area is A1:Q108
if N2 = 4 print area is A1:Q144
Then reset print area to nothing
Thank you in advance for your help

--
Rick
.