Print from Userform with Selected Printer

[indub]

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!

[indub]

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?

[indub]

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:

Please Login or Register  to view this content.

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?

[indub]

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:

Please Login or Register  to view this content.

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.

[indub]

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?

[gmr4evr1]

Would Exit Sub or Kill oWord work?

[indub]

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.

[gmr4evr1]

Glad you were able to get it worked out.

[SDdeRuiter]

I copied code above but still getting error:

Please Login or Register  to view this content.

Fault tracing ends on line:

Please Login or Register  to view this content.

telling me Fault 1120 Printerfault.
What could be wrong?

[gmr4evr1]

I think it's against forum rules to "piggy-back" on a thread. You should create a new thread with your question. Doing this will also get more views as the only ones that will se this thread are the ones that were involved in it.

[indub]

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.