I am trying to change active printer according to worksheet name when click on Quick Print button, however, the App_WorkbookBeforePrint
event is triggered twice. Tried App_WorkbookBeforeClose
also triggered twice. I have already changed the Error Trapping to Break on All Error, but it seems like no errors have occurred.
ThisWorkbook:
Private XLApp As CExcelEvents
Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub
Class Modules:
Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox Wb.FullName
assignPrinter
End Sub
Module:
Const printer1 As String = "Bullzip PDF Printer on Ne10:"
Const printer2 As String = "EPSONF7E8B5 (L565 Series) on Ne07:"
Public Sub assignPrinter()
Dim ws As Worksheet
Dim wsn As String
Set ws = ActiveWorkbook.ActiveSheet
wsn = ws.Name
Select Case wsn
Case "FGWIP"
Application.ActivePrinter = printer1
ws.PrintOut
Exit Sub
Case "Rework"
Application.ActivePrinter = printer2
ws.PrintOut
Exit Sub
Case Else
MsgBox "Else case."
Exit Sub
End Select
End Sub
Update:
Use App_SheetActivate
instead of App_WorkbookBeforePrint
to change active printer and remove ws.Printout
as mentioned by Matley
I re-created your modules and the only error I received was in the code Debug.Print assignPrinter in which I replaced with assignPrinter.
The rest of the codes works fine. App_WorkbookBeforePrint did not trigger twice. You can set a breakpoint in App_WorkbookBeforePrint then look into Stack to see which triggers App_WorkbookBeforePrint for the second time.