Preventing Users From Printing Your Excel Workbook Tabs With VBA

Disable Printing Capabilities In Excel Spreadsheet File

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 Sub

Where To Store These Macros

VBA ThisWorkbook Object Module

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 Sub

Prevent 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 Sub

Disable 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 Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

~ Chris  :)