Preventing Users From Printing Your Excel Workbook Tabs With VBA
Sometimes when you have models that are dealing with sensitive information, you want to take extra steps to prevent that data from getting passed around. In this article, you will be walked through different scenarios where you can prevent anyone from using the Print features to makes printouts of your Excel tabs.
A Couple Caveats To Disabling The Printing
First, I’ll state the obvious. While this solution might deter people from printing your worksheets, it will definitely not prevent people from taking screenshots and printing those. This solution is really just going to make it a hassle to print out your data and hopeful serves as a reminder to your audience that they shouldn’t be printing out the data stored in the workbook.
Secondly, the VBA you will be using is going to be triggered by the “Printing” action and therefore, it will only work if VBA Events are enabled in Excel. VBA Events are enabled by default and can only be turned off when macros are disabled or when your use VBA code to turn VBA Events off.
Most Excel users are not going to be advanced enough to know to turn off VBA Events, but it might be a good idea to create a physical button in your workbook that the user must click in order to use the file. A common practice would be to hide all the worksheets with the xlVeryHidden property and leave a single worksheet with a button that is assigned to a macro. Once clicked, the button could execute code to unhide all the worksheets and ensure that VBA Events are active. This code could look something like this:
Sub FileStartup()
Dim sht As Worksheet
'Optimize Code
Application.ScreenUpdating = True
'Loop Through Each Worksheet And Unhide
For Each sht In ThisWorkbook.Worksheets
sht.Visible = xlVisible
Next sht
'Ensure VBA Events Are Turned On
Application.EnableEvents = True
End SubWhere To Store These Macros
Unlike normal VBA macros and functions, event-triggered macros need to be stored in a Worksheet Object Module or in the case of this article, the Workbook Object Module. To get to this module, you will need to double-click on the ThisWorkbook Object name within the Microsoft Excel Objects folder in your Project Window (within the Visual Basic Editor). After you double-click, you will see a white sheet appear and you can proceed to store your event-triggered macros within the object. For further information on this subject, you can read my article discussing how to trigger your macros automatically with event handlers.
VBA Macro To Prevent Anything From Being Printed
I first going to start out with a VBA snippet that will disable printing for the entire workbook. It is going to be “triggered” on the BeforePrint event….which means the code will run before any printing can be had.
This code is actually quite simple in that it literally just changes the figurative Cancel button on (ie True) and then notifies the user that they cannot print any worksheet with this Excel file.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'PURPOSE: Prevent user from printing this workbook
'NOTES: This Code Must Reside in the ThisWorkbook Object
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'Cancel the Print Request
Cancel = True
'Notify User They Cannot Print this workbook
MsgBox "You do not have permission to print this file", vbOKOnly, "Cannot Print"
End SubPrevent Printing Of A Specific Worksheet
Now let’s take the previous macro code one step further and assume you only really care about one worksheet being printed. Provide a name of the worksheet you want to disable and the code will go through and check to see if that particular worksheet is currently selected to print.
If someone wanted to be cute, they could just change the name of the worksheet and bypass this macro’s functionality. To strengthen this macro even more, I would recommend referencing the code name instead of the text name as most people don’t know how to change a worksheet’s code name.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'PURPOSE: Prevent user from printing A Specific Worksheet
'NOTES: This Code Must Reside in the ThisWorkbook Object
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim WorksheetName As String
Dim sht As Worksheet
WorksheetName = "Sheet1"
For Each sht In ThisWorkbook.Windows(1).SelectedSheets
If sht.Name = WorksheetName Then
'Cancel the Print Request
Cancel = True
Exit For
End If
Next sht
'Notify User They Cannot Print this workbook
MsgBox "You do not have permission to print the [" _
& WorksheetName & "] worksheet", vbOKOnly, "Cannot Print"
End SubDisable Printing For Multiple Worksheets
Below is another variation that allows you to prevent the printing of multiple specifc worksheets within your Excel file. Again, you can strengthen this by using worksheet code name references instead of the worksheet name.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'PURPOSE: Prevent user from printing Specific Worksheets
'NOTES: This Code Must Reside in the ThisWorkbook Object
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim DoNotPrintList As String
Dim DisablePrint As Variant
Dim sht As Worksheet
'Store Names of worksheets that can't be printed
DisablePrint = Array("Sheet1", "Sheet3", "Sheet5")
'Create a list of all the sheets not allowed to be printed
For x = LBound(DisablePrint) To UBound(DisablePrint)
DoNotPrintList = DoNotPrintList & DisablePrint(x) & ";"
Next x
'Loop through each worksheet that is currently selected
For Each sht In ThisWorkbook.Windows(1).SelectedSheets
'Cancel the Print Request if Sheet Name Should not be printed
If DoNotPrintList Like "*" & sht.Name & ";*" Then
Cancel = True
Exit For
End If
Next sht
'Notify User They Cannot Print this worksheet
MsgBox "You do not have permission to print the [" _
& sht.Name & "] worksheet", vbOKOnly, "Cannot Print"
End SubAfter 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.