Specifying printer and printing userform using VBA

[kidwispa]

Hi all,

I am trying to print the userform below by clicking on the "Print" button:

Because I have managed to sort the conditional formatting out in the text boxes, I'll need to be able to print in colour, so will have to choose a printer other than my default option.

Originally the code I used was :

Private Sub CommandButton6_Click()
CommandButton6.Visible = False
CommandButton1.Visible = False

Me.PrintForm

CommandButton6.Visible = True
CommandButton1.Visible = True
End Sub

But this only prints to my default printer. I've searched online and on this forum and have found the following suggestion

Private Sub CommandButton6_Click()
CommandButton6.Visible = False
CommandButton1.Visible = False

Application.Dialogs(xlDialogPrinterSetup).Show

CommandButton6.Visible = True
CommandButton1.Visible = True
End Sub

However when I run this it brings up the different printer options but doesn't print when I click ok. Is there something else I need to add?

Thanks in advance for any assistance!

 

[kidwispa]

I have a message coming up saying:

"Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute"

???

 

[kidwispa]

I've managed to get past that last error but now I get the following:

"Compile error: Only comments may appear after End Sub, End Function, or End Property"

 

[RoryA]

Everything before the ChangePrinter sub needs to go at the top of the module.
Incidentally, for future reference, if you are using 64bit Office, you should really say so for any code questions as there can be differences.

 

[kidwispa]

Thanks Rory - I didn't know I was running 64-bit until this error came up (only had xl2010 installed a week or two ago through work).

I've moved the section of code you mentioned to the top of the module and now the "Compile error - 64-bit..." error is back again... don't know if this will help but when I click the play button it highlights the word Function in the 4th line of code;

Option Explicit
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A
Private Declare [COLOR=lime]Function[/COLOR] SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
ByVal hwnd As Long,

Hope that makes sense!

 

[RoryA]

For 64bit I think the declarations need to be:

Private Declare PtrSafe Function SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
ByVal hwnd As LongPtr, _
ByVal msg As Long, _
ByVal wParam As Long, _
lParam As Any) As LongPtr

Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" ( _
ByVal pszPrinter As String) As LongPtr

 

[kidwispa]

It works!!! Thanks for all your help Rory!!!

 

[RoryA]

Glad to help.

 

[kidwispa]

Glad to help.

Hi Rorya,

I'm just revisiting this and was wondering if you (or anyone else) could help with a small tweak? When I have selected my printer and printed the document, rather than immediately closing the userform I'd like a message box to appear to ask "Would you like to continue?" with the option Yes/No, and if they select Yes they would return to the UserForm, and No would close as per usual....



For anyone else here is the code that rorya has given me (that works brilliantly)...

Option Explicit
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A
Private Declare PtrSafe Function SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
ByVal hwnd As LongPtr, _
ByVal msg As Long, _
ByVal wParam As Long, _
lParam As Any) As LongPtr
Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" ( _
ByVal pszPrinter As String) As LongPtr
 
Public Sub ChangePrinter(NewPrinter As String)
SetDefaultPrinter NewPrinter
'broadcast the change
Call SendNotifyMessage(HWND_BROADCAST, _
WM_WININICHANGE, _
0, ByVal "windows")
End Sub
 
Private Sub CommandButton6_Click()
Dim OldPrinter As String, NewPrinter As String
CommandButton6.Visible = False
CommandButton1.Visible = False
CommandButton5.Visible = False
CommandButton7.Visible = False
OldPrinter = Left$(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)
Application.Dialogs(xlDialogPrinterSetup).Show
NewPrinter = Left$(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)
ChangePrinter NewPrinter
Me.PrintForm
CommandButton6.Visible = True
CommandButton1.Visible = True
CommandButton5.Visible = True
CommandButton7.Visible = True
ChangePrinter OldPrinter
End
End Sub

 

[RoryA]

Remove the End line (never ever ever ever ever ever use End on its own), and use:

If MsgBox("Would you like to continue?", vbyesno) = vbNo then unload me

 

[kidwispa]

Remove the End line (never ever ever ever ever ever use End on its own), and use:

If MsgBox("Would you like to continue?", vbyesno) = vbNo then unload me

Thanks again Rorya - this works perfectly!!