Setting print area depending on a cell value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


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
Click to expand...
 
I got it to work, problem was with merged cells. Thanks for the help.

Rick

anonymous@discussions.microsoft.com said:
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
Click to expand...
 
Back
Top