未执行打印却触发BeforePrint事件的Excel VBA问题

I ran into a tricky issue with my VBA code that exports the active worksheet to a PDF (with a filename built from the sheet's ID and specific cell values): the workbook's BeforePrint event was firing even when no actual print action was initiated, and it traced directly to the PDF export step. I'm on Windows 7 Ultimate + Excel 2016, have solid VBA experience, and tried removing parameters like IgnorePrintAreas:=False and OpenAfterPublish:=True—no luck fixing it.

My Original Code #

Option Explicit
Public Sub createpdffile()
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    Dim sheetname As String, sheetcode As String
    Dim iRow As Long
    Dim openPos As Integer
    Dim closePos As Integer
    'temporarily disable error handler so that I can see where the bug is.
    'On Error GoTo errHandler
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    wbA.Save
    'get last row of sheet and set print area to last row with L column
    iRow = wsA.Cells(Rows.Count, 1).End(xlUp).Row
    wsA.PageSetup.PrintArea = wsA.Range("A1:L" & iRow).Address
    'just checking name in sheet and removing needed characters
    sheetname = wsA.Name
    openPos = InStr(sheetname, "(")
    closePos = InStr(sheetname, ")")
    sheetcode = Mid(sheetname, openPos + 1, closePos - openPos - 1)
    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
        strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"
    'create default name for saving file
    strFile = sheetcode & " No. " & wsA.Cells(11, 9) & " - " & wsA.Cells(8, 3) & ".pdf"
    strPathFile = strPath & strFile
    'use can enter name and select folder for file
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")
    'export to PDF if a folder was selected
    'THIS IS WHERE THE ERROR IS LOCATED
    If myFile <> "False" Then
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        'confirmation message with file info
        MsgBox "PDF file has been created: " _
            & vbCrLf _
            & myFile
    End If
exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file" & vbNewLine & _
        "Please complete the details needed!", vbOKOnly + vbExclamation, "Error Saving as PDF"
    Resume exitHandler
End Sub

The Root Cause #

Excel uses a virtual print process under the hood when you call ExportAsFixedFormat to generate a PDF. This triggers the BeforePrint event automatically, even though you're not sending anything to a physical printer.

The Fix (Inspired by Foxfire and Burns and Burns) #

We can use a public boolean flag to "bypass" the BeforePrint event during PDF exports. Here's how to implement it:

1. Add the public flag at the top of your standard module (outside any subroutine):

   Public myboolean As Boolean
   

2. Set the flag at the start of your export procedure and reset it when done:
   Update your createpdffile sub to include these lines:

   Public Sub createpdffile()
       ' Add this line first to signal we're starting a PDF export
       myboolean = True
       
       Dim wsA As Worksheet
       ' ... rest of your original code ...
       
   exitHandler:
       ' Reset the flag so normal print operations work as expected
       myboolean = False
       Exit Sub
   errHandler:
       MsgBox "Could not create PDF file" & vbNewLine & _
           "Please complete the details needed!", vbOKOnly + vbExclamation, "Error Saving as PDF"
       ' Don't forget to reset the flag here too!
       myboolean = False
       Resume exitHandler
   End Sub
   

3. Modify the BeforePrint event in your ThisWorkbook module:

   Private Sub Workbook_BeforePrint(Cancel As Boolean)
       ' If we're in the middle of a PDF export, skip the event logic
       If myboolean = True Then
           Cancel = True ' Optional: cancels the virtual print trigger entirely
           Exit Sub
       End If
       
       ' Your original BeforePrint event code goes here
   End Sub
   

This flag acts as a toggle: when we're actively exporting to PDF, myboolean is True, so the BeforePrint event exits immediately. Once the export finishes (or errors out), we reset the flag to False so normal print operations work as intended.

内容的提问来源于stack exchange，提问作者Jem Eripol