User select printer in print macro

[sarah_tennessee]

It looks like I am trying to do the opposite of what other people are posting
about. I have created a print macro that enablesl the user to select which
worksheets they want to print through a checkbox form. This works great, but
the worksheets automatically print on the user's default printe.r I would
like the print dialog box to let the user select a printer. I have written
code (with much help from another user forum) that enables me to bring up the
print dialog box, but then nothing prints.

This code worked – the sheets printed – but printed to the default printer:

If CheckBox1.Value = True Then Sheets(Array("Total Summary", "VZ Total
Summary")).PrintOut Copies:=1



This code brings up the printer dialog box, but doesn’t print anything:

If CheckBox1.Value = True Then Sheets(Array("Total Summary", "VZ Total
Summary")).Application.Dialogs(xlDialogPrint).Show

Thoughts?
thanks in advance!

 

[rocket0612]

maybe this will help, say there was a choice of 2 printers, and you have
a check box to select which printer to print from


Code:
--------------------

'deterime which printer to print from
If Range("A1") = true then myprinterselect = "\\BTBEAPAPP04\PBTBEA001M"
If Range("A2") = true then myprinterselect = "\\BTBEAPAPP04\PBTBEA002M"


Dim sCurrentPrinter As String
sCurrentPrinter = ActivePrinter
wrd.ActivePrinter = my printer select

 

[sarah_tennessee]

Great idea, very clever...that will work for the onsite folks. The problem
is that we are going to be sending to offsite people and I have no idea what
their printers are. :-(

 

[JMB]

I think I used the printersetup dialog box in a project to allow users to
change the printer. I had a command button on the form that ran code like
this

Application.Dialogs(xlDialogPrinterSetup).Show

Then I put the activeprinter.name into a label that was on my form so users
knew what the active printer was. The code to print the worksheets was
attached to another command button.

Hope this helps.

 

[JMB]

Two other links posted by Norman Jones and Steve Yandl

http://word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm
http://tinyurl.com/9cm7l

 

[Tom Ogilvy]

Dim sh as Worksheet
set sh = Activesheet
If CheckBox1.Value = True Then
Sheets(Array("Total Summary", "VZ Total Summary")).Select
application.Dialogs(xlDialogPrinterSetup).Show
ActiveWindow.SelectedSheets.Print
End if
sh.Select

 

[Lee Meadowcroft]

This should be just what you want. It also restores the users default
printer after printing.

strOldActivePrinter = Application.ActivePrinter
Application.Dialogs(9).Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.ActivePrinter = strOldActivePrinter

Lee

 

[sarah_tennessee]

I have it working.
thanks for everyone's help!