select printer macro

[jatman]

morning,
i'm using Office 2007 on Vista, and i have the following event recorded in
"thisworkbook"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Purchase Order (Inventory)" Then
Cancel = True
Application.EnableEvents = False
With ActiveSheet
.PrintOut
Call POInv
End With
Application.EnableEvents = True
End If
End Sub


it works, but the problem is it sends the print job to the last used printer
or the default printer. i want to beable to select the printer before it
prints - maybe another macro gets called or something. any suggestions on
getting the macro to select a printer.

thank you,

jat

 

[JLatham]

You're probably going to have to start by recording some short macros to
determine the names of the available printers. Simply start recording a new
one, go to File | Print and choose a printer, then (to save paper) hit the
cancel button and stop recording. Examine the macro created and you'll have
the name for that printer, as (from one of mine)
Application.ActivePrinter = "\\X24800NO2\HP LaserJet 1020 on Ne03:"

You can then create a UserForm to appear, or perhaps a cell with list of
printers using data validation, to choose the printer from. Then just before
the With ActiveSheet section, set the chosen printer as the .ActivePrinter

Hope this helps some.