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!

 

[Norie]

Why don't you transfer the data to a worksheet and print that?

Much easier to format and you'll get a much better result - userforms aren't usually very pretty when printed.

You can even specify the printer to use via the ActivePrinter argument of the PrintOut method.

 

[kidwispa]

Thanks for the quick reply Norie - the layout of the userform when printed is fine so i don't need to make any changes to formatting, it's just that I need to choose an alternative printer to print in colour...

 

[Norie]

I think I once saw some code to do that, it involved a lot of Windows API calls I think - quite a fair bit of code as well.

If the data was on a worksheet you could specify the printer easily like using the argument I mentioned with PrintOut.

 

[kidwispa]

Thanks Norie,

I looked at the option of transferring the data to a worksheet but as the form is already formatted exactly how I want it to print, ideally I'd just like to be able to select from a list of printers on my network and print through there.

Can anyone help with this code? Here's what I have so far:

CommandButton6.Visible = False
CommandButton1.Visible = False
Application.Dialogs(xlDialogPrinterSetup).Show
CommandButton6.Visible = True
CommandButton1.Visible = True

When the printer setup option opens it allows me to choose the printer I want, but when I select the correct printer and press OK the setup window closes and nothing else happens.

Thanks again

 

[kidwispa]

I've been having a think and was wondering if it would be easier to change the default printer to the required printer when this document is opened (and back again once it closes) - that way I could just use the Me.PrintForm option...

Can anyone help with this?

 

[kidwispa]

Hi again,

Sorry to keep on about this but still unable to find a workable solution... I have found the above code online but this still only prints to my default printer... can anyone see what I have done wrong?

Private Sub CommandButton6_Click()
Dim strCurrentPrinter As String, strNetworkPrinter As String
    strNetworkPrinter = GetFullNetworkPrinterName("[URL="file://\\SOL3DC\SOL6"]\\SOL3DC\SOL6[/URL]")
    If Len(strCurrentPrinter) > 0 Then '
        strCurrentPrinter = Application.ActivePrinter
 
        Application.ActivePrinter = strNetworkPrinter
        UserForm1.PrintForm
        Application.ActivePrinter = strCurrentPrinter
 
End If
End
End Sub
 
Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
    strCurrentPrinterName = Application.ActivePrinter
    i = 0
    Do While i < 100
        strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
        On Error Resume Next
        Application.ActivePrinter = strTempPrinterName
        On Error GoTo 0
        If Application.ActivePrinter = strTempPrinterName Then
        GetFullNetworkPrinterName = strTempPrinterName
        i = 100
        End If
        i = i + 1
        Loop
End Function

 

[RoryA]

Something like this (based on an old Bob Phillips post):

Option Explicit

Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A

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

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

Public Sub ChangePrinter(NewPrinter As String)

SetDefaultPrinter NewPrinter

'broadcast the change
Call SendNotifyMessage(HWND_BROADCAST, _
WM_WININICHANGE, _
0, ByVal "windows")
End Sub
Private Sub CommandButton1_Click()
Dim OldPrinter As String, NewPrinter As String

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

ChangePrinter OldPrinter
End Sub

 

[kidwispa]

Thanks for the reply Rory,

Do you know which sections of the code I would need to change in order to make it relevant to me? (Have only been using VBA for about 2 months so still learning)

 

[RoryA]

You just need to alter the button_click at the bottom to refer to whatever button you are using. It will pop up a printer selection dialog for you to choose the printer you want.