Printing to a specific printer drawer

al97233

Board Regular
Joined
Nov 13, 2006
Messages
71
Hi Guys.

I have a workbook that has 8 worksheets that i use the code below to bring up a box that allows me to select which sheets I need to send to the printer and it has worked well. The problem I am having is when I print a different workbook or worksheet to a different printer (pdf factory pro) one of the sheets (sheet 3) loses its printer setting which have it printing to a selected drawer on the printer. This sheet is designed to be printed on a different color paper to differentiate it in our shop. is there a bit of code that could be inserted into the code below that would force sheet 3 to always be sent to the second drawer on our printer. The active printer is listed in the second line of the code which was added to make sure the workbook always printed to proper printer.

Thanks in advance for any help!
Al

Code:
Private Sub CommandButton1_Click()
    Application.ActivePrinter = "\\SBS2011\Sales Copier on Ne09:"
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False

'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

'   Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i

'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"
    End With

'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
    'Print as one print job (continuous page numbers)
    '        If PrintDlg.Show Then
    '            For Each cb In PrintDlg.CheckBoxes
    '                If cb.Value = xlOn Then
    '                    Worksheets(cb.Caption).Select Replace:=False
    '                End If
    '            Next cb
    '            ActiveWindow.SelectedSheets.PrintOut copies:=1
    '            ActiveSheet.Select
    '        End If
    'Print as separate print jobs
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
'                   ActiveSheet.PrintPreview 'for debugging
                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If

'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

'   Reactivate original sheet
    CurrentSheet.Activate
    Sheets(1).Activate
    
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You may want to think about setting up each tray as a different printer and then the code would select which printer to use... the only thing I found on editing printer settings in vba involved calls to windows api functions which is not difficult in theory but it requires a lot more code to setup structures and import functions and use it all properly.

Anyways if using the Shett.Printout function lets you change printer then maybe this article can help you setup the trays as their own printer. You make copies of the same printer with different settings essentially... https://superuser.com/questions/959078/treat-printer-tray-as-separate-printer
 
Upvote 0
I understand what you are saying but I do not see where I could insert that in the code so that only sheet 3 would be affected.
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

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
Back
Top