How To Prevent Users Printing An Excel Workbook

Welcome back, Excellers. Welcome to another #MacroMonday in my Excel series. As an Excel user, you may be wondering how to prevent other users from printing your workbook. This can be a helpful feature if you want to keep your data confidential or don’t want people to waste paper. Stay tuned for more tips and tricks for using Excel!

So, today, I will show you how to prevent users from printing an Excel workbook. This blog post responds to a subscriber request for this Excel solution. The solution was to write a very basic and simple Excel VBA Macro. I thought it would be a helpful share with you all. Please don’t get scared off. It’s a tiny bit of Visual Basic (VB) for Excel. It is straightforward. Why not give it a go?.

So, the easiest way to do this is to create a Macro to cancel the print job before it even starts.

Where Is The VBA Code Stored?.

This VBA code is saved in the current workbook to prevent users from printing it. Unlike regular VBA macros and functions, event-triggered Typically, we store macros in a Worksheet Object Module or the Workbook Object Module.

A lot of Excel Macros can be used over and over again. These Macros can be store in your Personal Macro Workbook. This will allow you to access these Macros every time you open Excel. Very handy. But in this case, we are working just with this one Excel workbook. It, therefore, makes sense to store the code in the workbook.

Starting The VBA Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this.

Either by hitting ALT +F11 or
Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

If you cannot see the Developer Tab, then you may need to enable it. Feel free to watch my YouTube video on enabling the Developer Tab.

You will now see the This Workbook code module. Double click to open it
The code will be written into This Workbook code module.

Using The Before Print Event.

The event that will trigger our code or macro to run is BeforePrint. This is exactly what is it. Before Printing!. Notice that once we enter the first line of code, Excel will automatically insert End Sub for us. The first part of the

This first line of code will literally change the status of the Cancel button to true to cancel user printing of the workbook. Cool huh?.

Private Sub Workbook_BeforePrint(Canel As Boolean)
Cancel = True

Using The Message Box.

The second part of the code is the Message Box. This will notify the user that they cannot print the workbook.

MsgBox "You can't print this worksheet", vbOKOnly, "Error"

If a user does try to print the workbook then the macro will automatically cancel it and display a message that says “You can’t print this workbook” A bit like this below.

So, the addition of the message box is helpful to keep the user informed that printing the Excel workbook is not an option.

Ending The Macro.

Finally, the code ends once the message box has been displayed with the “End Sub” piece of code. This was already entered into the module when the name of the macro was set.

prevent users printing an Excel workbook VBA Macro

Full Code To Stop A Workbook Being Printed.

Here is he full codes to stop user printing a An Excel workbook.

Private Sub Workbook_BeforePrint(Canel As Boolean)
Cancel = True
MsgBox "You can't print this worksheet", vbOKOnly, "Error"
End Sub

How To Excel At Excel