Allow user to select printer but print with my defined settings

[Brumbot]

All,

I have created a spreadsheet from which I want to print a report with set settings. What I would like is for the user to be able to select the printer they wish to print from (from the ones available to them - this could be printed anywhere around a large company) but then for the document to be printed with the settings that I choose for them. Is this possible? Here is my current code (note the use of "Application.Dialogs(xlDialogPrint).Show" to bring up the dialog box, but this will change the print settings I have defined).

Please Login or Register  to view this content.

Thanks in advance.

[ragulduy]

Below is the code I use for getting the list of printers a user has. It's not my code but unfortunately I can't remember where I got it from. Anyway it may be of help - at the end I use the loop to populate a listbox on a userform that the user can then use to select a printer.

Please Login or Register  to view this content.

End Sub

[Brumbot]

Hi yudlugar,

So do I need to create a form with a listbox on it and enter this code in there? Bit confused as to how I put this into mine. Thanks for the help though, it looks great.

[ragulduy]

I have it in a separate module, so if I make a print macro it would have:

Please Login or Register  to view this content.

depending on how you set up the userform, you need to put some code in it for when the user clicks a button, so mine has a commandbutton with ok and the code:

Please Login or Register  to view this content.

which takes me back to the print macro.

[JosephP]

not perfect but you could use

Please Login or Register  to view this content.

to select the printer

[Brumbot]

Thanks for your help yudlugar. Your advice pointed me towards another site that helped me.

http://www.cpearson.com/excel/GetPrinters.aspx

Very useful document in there that explained it all. Thanks.