Print from Userform with Selected Printer

Hello,

I am trying to print an excel userform to a specific printer instead of the default printer. Currently I double click any row of data on the worksheet and the userform will open with that row data to the corresponding userform labels as captions. I have a print button on the form using UserForm.PrintForm which prints it out to the default printer without issue. I now have a thermal printer for labels which I'd like to print the userform to instead, but I don't want the thermal printer as my default printer. Is there a way to specify a printer for a userform?

I don't want to send a printscreen of the userform to another worksheet just to print it to the thermal printer, that would defeat the purpose of even using the userform in the first place. I'm really just trying to add an extra step to UserForm.PrintForm for my print commandbutton so I can select the correct printer. If you have any other suggestions please let me know.

Thanks in advance!

It doesn't look like there's a direct way of doing this via PrintForm since PrintForm defaults to the control panel's default printer and appears to be a standalone function. Is there instead a way to make the "print" commandbutton on the userform change the default printer and then change it back after the userform prints?

Well I decided to just make my default printer the thermal printer so the userform prints without issue as a label, and other programs still allow the choice of printer before printing...except for other Microsoft programs like IE. If I leave the default printer as the thermal and print something from IE, even if I select a different printer, it keeps the page size as the thermal size. Based on that, its obviously all connected to windows API.

I went ahead and coded excel's vba to take a screenshot of the userform, paste on a new page and print to a desired printer, but the window of the userform prints as well. Also, despite having FitToPage set at 1, the print is very small. Here's the current code I have for my userform commandbutton that prints:

Private Sub CommandButton1_Click()
    Dim strPrintArea
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
        KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
        KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
        DisplayAsIcon:=False
    ActiveSheet.Range("A1").Select
    ActiveSheet.PageSetup.Orientation = xlPortrait
    
   
With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With

    ActiveSheet.Shapes(1).Select    'Will always be Shape 1
     
    With Selection  'Get print area of picture
        strPrintArea = .TopLeftCell.Address & ":" & .BottomRightCell.Address
    End With
     
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
     
    With ActiveSheet.PageSetup
        .PrintArea = strPrintArea  'Set Print Area
        .PrintGridlines = False
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.Dialogs(xlDialogPrinterSetup).Show
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWindow.Close False
End Sub

How can I make the either the screenshot or the pasted image of the screenshot only print the non-window userform? Similar to what userform.printform does.

Also, how can I scale the image to fit the correct paper size since FitToPage isn't working right?

Disregard the above code, it works for screenshot but the coding part is ridiculous to do what I want. I instead just found this online, which works perfect! The following code is pasted into the commandbutton's sub, but do not use Option Explicit. You must also have Microsoft word to use this:

Private Sub CommandButton1_Click()
Dim fileOK As Boolean
Dim sPrinter As String

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

If fileOK = True Then
    ChangeDefaultPrinter (Application.ActivePrinter)
    CommandButton1.Visible = False
    UserForm1.PrintForm
    CommandButton1.Visible = True
    Unload UserForm1
    ChangeDefaultPrinter (sPrinter)
End If

End Sub


Public Sub ChangeDefaultPrinter(pName As String)

Set oWord = CreateObject("Word.Application")
oWord.WordBasic.FilePrintSetup Printer:=pName, DoNotSetAsSysDefault:=0
Set oWord = Nothing
   
End Sub

The only problem with this is it changes the default printer to whatever you select. I plan on using this for all my userforms so it'll change the default accordingly, but if you know of a way within the code above to make it change back to the original default printer. Please let me know as I would prefer that.

I need some help regarding the Word portion of the code above.

My computer started running very slow today so I checked the active processes and WINWORD.EXE was listed hundreds of times. Apparently after printing userforms using the code above it leaves an open process for Word. What can I add to the 'oWord' code portion above so the process ends once the userform prints?

Thanks for the suggestions. Those two in particular didn't work, but it put me on the right track. I place 'oWord.Quit' a line above 'oWord = Nothing' and it ends the word process after printing.

I copied code above but still getting error:

Private Sub CommandButton1_Click()
Dim fileOK As Boolean
Dim sPrinter As String

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

If fileOK = True Then
   ChangeDefaultPrinter (Application.ActivePrinter)
    CommandButton1.Visible = False
    CommandButton2.Visible = False
    UserForm1.PrintForm
    CommandButton1.Visible = True
    CommandButton2.Visible = True
    Unload Cat1_Rapportage
    ChangeDefaultPrinter (sPrinter)

End If

End Sub

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

   
End Sub

Fault tracing ends on line:

oWord.WordBasic.FilePrintSetup Printer:=pName, DoNotSetAsSysDefault:=0

telling me Fault 1120 Printerfault.
What could be wrong?

telling me Fault 1120 Printerfault.
What could be wrong?

Unfortunately, I'm not sure. It may depend on the version of excel and word you have, you need both installed. I am still using this method on multiple sheets with Excel and Word 2010, works great. As the other poster mentioned, it probably will be best for you to post your question as a new thread to get more traffic.