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