setting print area via VBA
 

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

setting print area via VBA
 

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




"JulieD" wrote in message ...
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

setting print area via VBA
 

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

"Rodney POWELL" wrote in message ...
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




"JulieD" wrote in message ...
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

setting print area via VBA
 

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

printarea is looking for a string.

--

Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

setting print area via VBA
 

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


--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

setting print area via VBA
 

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


"Don Guillett" wrote in message
...
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


--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

setting print area via VBA
 

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

activesheet.pagesetup.printarea = "PackingList"


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



.

setting print area via VBA
 

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

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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


"Don Guillett" wrote in message
...
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


--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

setting print area via VBA
 

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

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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


"Don Guillett" wrote in message
...
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


--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

setting print area via VBA
 

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

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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


"Don Guillett" wrote in message
...
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


--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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