Search code examples
excelvbaexcel-addins

Excel VBA application event triggered twice


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


Solution

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