Macro to print but ask which printer 1

[SQLWilts]

Hi,

I have a spreadsheet that prints to the default printer when a button is pressed. Up until now this has been fine, but now we are installing network printers and the user in question will have a b&w Laser as her default. I now want to change the macro to ask which printer to print to - which is where I am a little stuck as my VBA at this level is less than brilliant!
Code I have used is as follows:

Application.ActivePrinter = "\\ipp://10.1.9.50\HPLJ410DTN on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\ipp://10.1.9.50\HPLJ410DTN on Ne05:", Collate:=True

This sends to HPLJ410DTN printer, but I don't want to hard code the printer name in there.

Any help is gratefully received

 

[dk87]

Insert a Common Dialog control, and use the ShowPrinter method. (Toolbox - More Controls - Microsoft Common Dialog Control)

 

[SQLWilts]

Thanks for your reply dk87, but that went straight over my head. Where is the toolbox in Excel 2002? Maybe I haven't got it installed?

 

[dk87]

Click on View-Toolbars-Control Toolbox, then edit your code to show the printer box. For example, if you have a button that you named cmdPrint, your code would simply look like this:

Private Sub cmdPrint_Click()
CommonDialog1.ShowPrinter

End Sub

and all your current printers are displayed.

 

[dk87]

Sorry, I wasn't clear on that last one, the clicking on view-toolbars-control toolbox only shows the toolbox, you will still need to add the common dialog control. By the way, don't worry about hiding the control, when you exit design mode it should hide automatically.

 

[PHV]

And what about this ?
Application.Dialogs(xlDialogPrint).Show

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SQLWilts]

Thanks PHV that worked great! Gave you a star for that one