Setting Application.ActivePrinter and .PrintForm

[tstuarts]

Hello,

I have a "Print Form" command button on an excel vba form. I am trying to send the form to a printer other than the default printer using:

Application.ActivePrinter = "HP LaserJet 8000 Series PCL on LPT4:"


NewSparePart.PrintForm


The problem is that the .PrintForm method ignores the setting of the ActivePrinter and prints to the default printer.

Can anyone please offer any suggestions? Thank you.

[Bob Phillips]

you could use this to let them select a printer



Dim fOK As Boolean
Dim sPrinter As String

With Application
sPrinter = .ActivePrinter
fOK = .Dialogs(xlDialogPrinterSetup).Show
End With

If fOK Then
Me.PrintForm
Application.ActivePrinter = sPrinter
End If

[markmrw]

I have had this problem
here is what i have used at work

Application.ActivePrinter = "\\ComputerName\HP LaserJet 8000 Series PCL on LPT4: (file://\\ComputerName\HP LaserJet 8000 Series PCL on LPT4:)"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\ComputerName\HP (file://\\ComputerName\HP) LaserJet 8000 Series PCL on LPT4:", Collate:=True

Hope this helps
mark

[tstuarts]

xld:

I can select a printer from the dialog box, but it still ignores and prints to the default.
It is interesting, if I send a worksheet instead of the form, this works great:

If fOK Then
'Me.PrintForm
Sheets("Destock Request").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.ActivePrinter = sPrinter
End If

Evidently the form is not considered active??

[tstuarts]

Thanks Mark, but I don't think the printer path is the issue, because it works with other objects.

[lucas]

Printing forms is not the best idea anyway in my humble opinion.......if you are talking about printing the userform.....

put the data into a sheet and you can format it to look the way you want it and save the ink......then delete the temp sheet if necessary.

[Bob Phillips]

I dug out this technique fromr the depths of my library



Option Explicit

Private Type PRINTER_INFO_4
pPrinterName As Long
pServerName As Long
Attributes As Long
End Type

Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A
Private Const PRINTER_LEVEL4 = &H4
Private Const PRINTER_ENUM_LOCAL = &H2

Private Declare Function SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
ByVal hwnd As Long, _
ByVal msg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" ( _
ByVal pszPrinter As String) As Long

Public Sub test()
Dim OldPrinter As String

OldPrinter = Left$(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)

ChangePrinter "PDFCreator" '"HP LaserJet 8000 Series PCL"

NewSparePart.PrintForm

ChangePrinter OldPrinter
End Sub


Public Sub ChangePrinter(NewPrinter As String)

SetDefaultPrinter NewPrinter

'broadcast the change
Call SendNotifyMessage(HWND_BROADCAST, _
WM_WININICHANGE, _
0, ByVal "windows")
End Sub