setting print area via VBA

J

JulieD

Hi All

having trouble (Excel 2000) setting the print area
i create a form, populated with values from a database and then copy this
form to a new workbook. In the original form i created a named range,
which, of course goes into the new book. What i want to do is use the name
range to set the print area for the form in the new workbook.

This process is all run from the OKbutton on a userform.

For some reason i keep getting a "can not set print area" message no matter
which way i try

currently my code (after trying everything i can think of) goes like this
sheets("packinglist").activate
sheets("packinglist").copy
unload userform2
msgbox "You can now print the packing list"
i =workbooks.count
workbook(i).activate
sheets("packinglist").activate
activesheet.pagesetup.printarea = range("PackingList")

any ideas?

Thanks
JulieD
 
R

Rodney POWELL

Julie --


You're very close ...


With Worksheets("packinglist")

.PageSetup.PrintArea = .Range("PackingList").Address

End With


-----

Hope it Helps,

- Rodney POWELL
Microsoft MVP - Excel

Beyond Technology
Spring, Texas USA
www.BeyondTechnology.com




Hi All

having trouble (Excel 2000) setting the print area
i create a form, populated with values from a database and then copy this
form to a new workbook. In the original form i created a named range,
which, of course goes into the new book. What i want to do is use the name
range to set the print area for the form in the new workbook.

This process is all run from the OKbutton on a userform.

For some reason i keep getting a "can not set print area" message no matter
which way i try

currently my code (after trying everything i can think of) goes like this
sheets("packinglist").activate
sheets("packinglist").copy
unload userform2
msgbox "You can now print the packing list"
i =workbooks.count
workbook(i).activate
sheets("packinglist").activate
activesheet.pagesetup.printarea = range("PackingList")

any ideas?

Thanks
JulieD
 
J

JulieD

Hi Rodney

thanks, i think that must have been about the only thing i didn't think of trying :)

i'll now stop banging my head on the wall.

Cheers
JulieD

Julie --


You're very close ...


With Worksheets("packinglist")

.PageSetup.PrintArea = .Range("PackingList").Address

End With


-----

Hope it Helps,

- Rodney POWELL
Microsoft MVP - Excel

Beyond Technology
Spring, Texas USA
www.BeyondTechnology.com




Hi All

having trouble (Excel 2000) setting the print area
i create a form, populated with values from a database and then copy this
form to a new workbook. In the original form i created a named range,
which, of course goes into the new book. What i want to do is use the name
range to set the print area for the form in the new workbook.

This process is all run from the OKbutton on a userform.

For some reason i keep getting a "can not set print area" message no matter
which way i try

currently my code (after trying everything i can think of) goes like this
sheets("packinglist").activate
sheets("packinglist").copy
unload userform2
msgbox "You can now print the packing list"
i =workbooks.count
workbook(i).activate
sheets("packinglist").activate
activesheet.pagesetup.printarea = range("PackingList")

any ideas?

Thanks
JulieD
 
T

Tom Ogilvy

activesheet.pagesetup.printarea =
range("PackingList").Address(external:=True)

printarea is looking for a string.
 
D

Don Guillett

You don't have to go there. Try this for named range.

Sub printpackinglist()
'Sheets("packinglist").Range("packinglistprintarea").Printpreview
Sheets("packinglist").Range("packinglistprintarea").PrintOut
End Sub
 
J

JulieD

Hi Don

i can't :(
i don't have a printer installed on the machine i'm programming on (don't
ask!) ... so i can't run code with those two lines in it.

Cheers
JulieD
 
F

Frank Stone

hi.
you got a lot of suggestions on this on. Here's mine
i use this syntax every moning.

activesheet.pagesetup.printarea = "PackingList"
 
D

Don Guillett

You don't have to have a printer installed. Just install a printer driver.
In other words, install a printer that you don't have and use the
printpreview line
 
J

JulieD

don't have permissions to install a printer driver .... :)

sad isn't it (but i do have a lovely new chair, a stapler & a sticky tape
dispenser)

Cheers
JulieD
 
D

Don Guillett

Just one of the reasons that I always worked for ME.
BTW, can I borrow your sticky tape dispenser sometime?
 
Top