Neowok -

Alternately, you can invoke the Excel dialog and let your user select from
the known printers

strOldActivePrinter = Application.ActivePrinter
Application.Dialogs(9).show

If you want to capture the name by which Excel knows the selected printer -
you could also set a text
box's text to the new value

strNewActivePrinter = Application.ActivePrinter

After you print the selected sheets, be sure to return the ActivePrinter to
its original setting with

Application.ActivePrinter = strOldActivePrinter

Otherwise, your users will curse your name : )

James Cox


"neowok" wrote in message
...

right thanks. I've decided to use Franks approach and create a userform
where the user selects which printer they want and then the correct
sheets in the spreadsheet are printed to that printer, so i have tried
to use this code

If UserForm1.OptionButton1.Value = True Then Application.ActivePrinter
= "\\TLPRWFC020001\tlnpwfc020001"

however i get an error when it runs saying "Method 'Activeprinter' of
object '_Application' failed".

any ideas whats wrong with my line of code?

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile:
http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=272024