You can pop up the select printer dialog and let them choose.

Application.Dialogs(xlDialogPrint).Show
or
Application.Dialogs(xlDialogPrinterSetup).Show




CJLuke wrote:

I have developed a spreadsheet uses a VBA print button, and the following is
the code currently assigned to it:

Application.ActivePrinter = "\\txhoro01\TX-HO-ROAKS-JD23 on Ne07:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\txhoro01\TX-HO-ROAKS-JD23 on Ne07:", Collate:=True

It selects this printer, and does a good job of printing here in my office.
The problem I have is that the spreadsheet will be distributed to other
people in different places who obviously have different printers. Is there a
way I can create a message box or something similar that will show a list of
the available printers on the network so you can simply select the printer
you would like to print to?

--

Dave Peterson

Actually, that code doesn't print any pages--it just allows the user to change
printers.

In your existing code, you used
activewindow.selectedsheets....

You could either tell the user to select the sheets to print or you could take
control (in your code) and print them yourself:

worksheets(array("sheet1", "another sheet", "some other sheet")).printout

CJLuke wrote:

Ok, that works just fine, but there is another issue. This only prints the
active sheet, and I don't want the active sheet to print. I need two other
sheets in the workbook to print. Is there any way to do this and still be
able to select the printers?

"Dave Peterson" wrote:

You can pop up the select printer dialog and let them choose.

Application.Dialogs(xlDialogPrint).Show
or
Application.Dialogs(xlDialogPrinterSetup).Show




CJLuke wrote:

I have developed a spreadsheet uses a VBA print button, and the following is
the code currently assigned to it:

Application.ActivePrinter = "\\txhoro01\TX-HO-ROAKS-JD23 on Ne07:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\txhoro01\TX-HO-ROAKS-JD23 on Ne07:", Collate:=True

It selects this printer, and does a good job of printing here in my office.
The problem I have is that the spreadsheet will be distributed to other
people in different places who obviously have different printers. Is there a
way I can create a message box or something similar that will show a list of
the available printers on the network so you can simply select the printer
you would like to print to?

--

Dave Peterson


--

Dave Peterson