Add Duplex command to Print Button (VBA)

seanjon

New Member
Joined
Dec 23, 2017
Messages
10
Good day.

A workbook I have has 22 worksheets. I want pages 1&2, 3&4, 5&6, etc. to print both sides. I have a color printer on the network that is not my default printer. I made a command button that will send the workbook to the color printer. I also want it to print it duplex.

This is what I have for the button so far. It works and sends the workbook to that printer.

Code:
Private Sub CommandButton1_Click()
Dim sCurrentPrinter As String
Const MyPrinter As String = "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:"
sCurrentPrinter = Application.ActivePrinter
Application.ActivePrinter = MyPrinter
ActiveWorkbook.PrintOut From:=1, To:=22, Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Application.ActivePrinter = sCurrentPrinter
End Sub


Through searching, I know that there is a code to set it to duplex:


Code:
Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Where should I put this in the code for my command button, or is there something different I need to put in the sub?

Thanks for your time.

Sean
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
You may be able to send control codes to the printer to tell it to print in duplex mode, but you would have to look through the printer documentation to see if this is possible.

Otherwise it is probably possible using the dark art of API calls, which I can only copy from the Wizards that write them!
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
BTW

Code:
Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Won't work without the function SetPrinterDuplex
 
Last edited:

seanjon

New Member
Joined
Dec 23, 2017
Messages
10
BTW

Code:
Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Won't work without the function SetPrinterDuplex

I am not understanding what you mean by this. The code has setprinterduplex. What am I missing that you are telling me? Thanks
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
A quick search turned up this function:

Code:
<code class="" style="box-sizing: border-box; font-size: inherit; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;">   Public Function SetPrinterDuplex(ByVal sPrinterName As String, _
   ByVal nDuplexSetting As Long) As Boolean

      Dim hPrinter As Long
      Dim pd As PRINTER_DEFAULTS
      Dim pinfo As PRINTER_INFO_2
      Dim dm As DEVMODE
   
      Dim yDevModeData() As Byte
      Dim yPInfoMemory() As Byte
      Dim nBytesNeeded As Long
      Dim nRet As Long, nJunk As Long
   
      On Error GoTo cleanup
   
      If (nDuplexSetting < 1) Or (nDuplexSetting > 3) Then
         MsgBox "Error: dwDuplexSetting is incorrect."
         Exit Function
      End If
      
      pd.DesiredAccess = PRINTER_ALL_ACCESS
      nRet = OpenPrinter(sPrinterName, hPrinter, pd)
      If (nRet = 0) Or (hPrinter = 0) Then
         If Err.LastDllError = 5 Then
            MsgBox "Access denied -- See the article for more info."
         Else
            MsgBox "Cannot open the printer specified " & _
              "(make sure the printer name is correct)."
         End If
         Exit Function
      End If
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
      If (nRet < 0) Then
         MsgBox "Cannot get the size of the DEVMODE structure."
         GoTo cleanup
      End If
   
      ReDim yDevModeData(nRet + 100) As Byte
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
                  VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
      If (nRet < 0) Then
         MsgBox "Cannot get the DEVMODE structure."
         GoTo cleanup
      End If
   
      Call CopyMemory(dm, yDevModeData(0), Len(dm))
   
      If Not CBool(dm.dmFields And DM_DUPLEX) Then
        MsgBox "You cannot modify the duplex flag for this printer " & _
               "because it does not support duplex or the driver " & _
               "does not support setting it from the Windows API."
         GoTo cleanup
      End If
   
      dm.dmDuplex = nDuplexSetting
      Call CopyMemory(yDevModeData(0), dm, Len(dm))
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
        VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
        DM_IN_BUFFER Or DM_OUT_BUFFER)

      If (nRet < 0) Then
        MsgBox "Unable to set duplex setting to this printer."
        GoTo cleanup
      End If
   
      Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
      If (nBytesNeeded = 0) Then GoTo cleanup
   
      ReDim yPInfoMemory(nBytesNeeded + 100) As Byte

      nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
      If (nRet = 0) Then
         MsgBox "Unable to get shared printer settings."
         GoTo cleanup
      End If
   
      Call CopyMemory(pinfo, yPInfoMemory(0), Len(pinfo))
      pinfo.pDevmode = VarPtr(yDevModeData(0))
      pinfo.pSecurityDescriptor = 0
      Call CopyMemory(yPInfoMemory(0), pinfo, Len(pinfo))
   
      nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
      If (nRet = 0) Then
         MsgBox "Unable to set shared printer settings."
      End If
   
      SetPrinterDuplex = CBool(nRet)

cleanup:
      If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)

   End Function
</code>
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top