Shanan212

asked on 12/21/2012

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
       Me.PrintForm
       Application.ActivePrinter = sPrinter
    End If
End Sub

Open in new window

Hi,

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)

Thanks!

Visual Basic Classic

Last Comment

shorvath

8/22/2022 - Mon

Barry62

12/21/2012

try reversing the printform and activeprinter statements:

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

Open in new window

Shanan212

12/21/2012

ASKER

No use Barry,

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

Thanks though!

Barry62

12/21/2012

try xlDialogPrinterSetup

Your help has saved me hundreds of hours of internet surfing.

fblack61

Shanan212

12/21/2012

ASKER

Nope, no help. Is there anyway to capture the printer selected?

As an example

    Application.Dialogs(xlDialogInsertPicture).Show
    Set MyPicture = Selection

Open in new window

I tried selection (msgbox selection) but gave an error in terms of getting printer name

Barry62

12/21/2012

Set Application.Printer = Application.Printers(sPrinter)

Shanan212

12/27/2012

ASKER

I am getting an error 438
Object doesn't support this property or method on the line

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

shorvath

12/27/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Shanan212

12/27/2012

ASKER

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

shorvath

12/27/2012

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