Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

Select printer and print userform

Sub CommandButtomln13_Click()
    Dim fileOK As Boolean
    Dim sPrinter As String
    With Application
        sPrinter = .ActivePrinter
        fileOK = .Dialogs(xlDialogPrint).Show
    End With
    If fileOK = True Then
       Application.ActivePrinter = sPrinter
    End If
End Sub

Open in new window


I have the above code under a button click sub. The purpose is to print the particular userform (the button is on)

I am showing printer dialog in an effort to switch printers should it be required. However, its not working. Regardless of what printer I choose, the userform is being printed on my default printer.

Any help is appreciated (no API calls please as user could be 32/64 bit)

Avatar of Barry62
Flag of United States of America image

try reversing the printform and activeprinter statements:

If fileOK = True Then
       Application.ActivePrinter = sPrinter
    End If

Open in new window

Avatar of Shanan212


No use Barry,

I selected PDF printer after inserting that code and it printed to my physical printer.

Thanks though!
try xlDialogPrinterSetup
Nope, no help. Is there anyway to capture the printer selected?

As an example

    Set MyPicture = Selection

Open in new window

I tried selection (msgbox selection) but gave an error in terms of getting printer name
Set Application.Printer = Application.Printers(sPrinter)
I am getting an error 438
Object doesn't support this property or method on the line
Avatar of shorvath
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks it worked. Only thing is that 'oWord' is not defined

Public Sub ChangeDefaultPrinter(pName As String)
    Dim oWord As Object
    Set oWord = CreateObject("Word.Application")
    oWord.Visible = False
    oWord.WordBasic.FilePrintSetup Printer:=pName, DoNotSetAsSysDefault:=0
    Set oWord = Nothing
End Sub

Open in new window

Oops, sorry,  not everyone uses Option Explicit when coding...