时间戳

首页 > 解决方案 > Excel VBA 应用程序事件触发了两次

excel - Excel VBA 应用程序事件触发了两次

问题描述

单击“快速打印”按钮时，我试图根据工作表名称更改活动打印机，但是，该App_WorkbookBeforePrint事件被触发了两次。试过App_WorkbookBeforeClose也触发了两次。我已经将 Error Trapping 更改为 Break on All Error，但似乎没有发生任何错误。

本工作簿：

Private XLApp As CExcelEvents

Private Sub Workbook_Open()
  Set XLApp = New CExcelEvents
End Sub

类模块：

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

模块：

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

更新：

如 Matley 所述，使用App_SheetActivate而不是App_WorkbookBeforePrint更改活动打印机并删除ws.Printout

标签： excelvbaexcel-addins

解决方案

我重新创建了您的模块，我收到的唯一错误是在代码Debug.Print assignPrinter中，我将其替换为assignPrinter。

其余代码工作正常。App_WorkbookBeforePrint没有触发两次。您可以在App_WorkbookBeforePrint中设置断点，然后查看 Stack 以查看第二次触发App_WorkbookBeforePrint的位置。

推荐阅读