[Excel VBA] How to Restrict Printing Unless Specific Conditions Are Met (BeforePrint Event)

Introduction

There is often a need to set specific rules for Excel files, such as “Do not allow printing unless the approver’s name is entered” or “Prohibit printing until a specific date is input.”

By using the VBA “Event” mechanism, you can easily implement such printing restrictions. In this article, I will explain how to cancel a print job using the Workbook_BeforePrint event, which executes automatically just before printing.

[Important] Where to Write the Code

Unlike standard macros that are executed by clicking a button, this code must be written in the ThisWorkbook module. This is a special location for monitoring the behavior of the entire workbook (in this case, “printing”).

1. Press Alt + F11 in Excel to open the VBE (Visual Basic Editor).
2. From the “Project Explorer” on the left, double-click ThisWorkbook.
3. Paste the code below into the white code window on the right.

Completed VBA Code

The following code cancels printing if cell E5 on the “InputSheet” is empty.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim checkCell As Range
    
    ' Set the sheet and cell to check
    ' Change "InputSheet" to your actual sheet name
    Set targetSheet = ThisWorkbook.Worksheets("InputSheet")
    Set checkCell = targetSheet.Range("E5")

    ' --- Check Process ---
    ' If the target cell is empty
    If IsEmpty(checkCell.Value) Then
    
        ' 1. Display a message to the user
        MsgBox "Please enter the Approver Name in cell " & checkCell.Address(False, False) & " before printing.", _
               vbExclamation, _
               "Input Error"
        
        ' 2. Move to the target sheet and cell
        targetSheet.Activate
        checkCell.Select
        
        ' 3. Cancel the print process
        Cancel = True
        
    End If

End Sub

Explanation of Key Points

1. Workbook_BeforePrint Event

Private Sub Workbook_BeforePrint(Cancel As Boolean)

This is a special Sub procedure that Excel automatically calls the moment a user presses the print button or attempts to view the print preview on any sheet in the workbook.

2. Cancel As Boolean Argument

The most important part of this event is the Cancel argument passed to it.

• Cancel is a special variable (flag) that tells Excel whether to continue or stop the print process.
• When the event starts, Cancel is False (do not stop).
• If you write Cancel = True in the code, it signals Excel to “Abort this print job!” Upon receiving this command, Excel interrupts all subsequent printing processes.

3. Conditional Branching and User Notification

If IsEmpty(checkCell.Value) Then
    ' ...
    Cancel = True
End If

An If statement is used to create a branch: “If the check target cell is empty.” Only when this condition is met (i.e., there is an omission), the following three actions are taken:

1. MsgBox: Inform the user via a message why printing is not allowed.
2. Activate/Select: Display the target sheet and select the specific cell so the user can enter the data immediately.
3. Cancel = True: As mentioned above, this stops the print job itself.

Summary

By using the Workbook_BeforePrint event, you can trigger your own check processes using the act of printing as a trigger.

• Create the Workbook_BeforePrint procedure in the ThisWorkbook module.
• Write conditional branching, such as checking for required inputs, inside the procedure.
• If conditions are not met, notify the user with MsgBox and stop printing with Cancel = True.

Using this mechanism enforces file operation rules and prevents the output of incomplete documents containing errors or omissions.