We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
10 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Select printer and print userform
Posted on 2012-12-21
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
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
2
9 Comments
try reversing the printform and activeprinter statements:
If fileOK = True Then
Application.ActivePrinter = sPrinter
Me.PrintForm
End If
No use Barry,
I selected PDF printer after inserting that code and it printed to my physical printer.
Thanks though!
I selected PDF printer after inserting that code and it printed to my physical printer.
Thanks though!
Nope, no help. Is there anyway to capture the printer selected?
As an example
I tried selection (msgbox selection) but gave an error in terms of getting printer name
As an example
Application.Dialogs(xlDialogInsertPicture).Show
Set MyPicture = Selection
I tried selection (msgbox selection) but gave an error in terms of getting printer name
Shanan212
I know this sounds lame but you can't change the default printer using Excel VBA.
However, you can change it using Word VBA (don't ask me, ask Microsoft)
http://support.microsoft.com/kb/216026
This code does what you want and has no API calls, but you do have to have Microsoft Word installed as well as Excel. (Most user have both)
I know this sounds lame but you can't change the default printer using Excel VBA.
However, you can change it using Word VBA (don't ask me, ask Microsoft)
http://support.microsoft.com/kb/216026
This code does what you want and has no API calls, but you do have to have Microsoft Word installed as well as Excel. (Most user have both)
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)
Me.PrintForm
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
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
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library. This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.
Â
There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.