Categories
Excel

Preventing Users from Printing a Workbook

Learn how to grant people access to your workbook while removing their ability to print its content. Also, how to use VBA to disable printing for the entire workbook or a particular worksheet.

1) Restrict Access: Removing Ability to Print Workbook

If you have access to an Information Rights Management Server, you may be able to grant people access to your workbook while removing their ability to edit, copy, or print it. First open the workbook that you want to disable printing option, then follow these steps:

  1. Click the File tab and then Info.
  2. Click on Protect Workbook and then Restrict Access.
  3. In the Permission dialog box, click on More Options.
  4. Uncheck the Print content option and
  5. Click OK.

By doing this, you will disable the Print command and the Print Preview feature for the workbook.

Note:
If you see “your machine isn’t set up for Information Rights Management”, try activating the Rights Management service (RMS) in the admin center of Microsoft 365. For more information, visit https://learn.microsoft.com/en-us/microsoft-365/enterprise/activate-rms-in-microsoft-365

2) Disable Print Option using VBA

If you want to prevent users from printing your workbook, using Before Print event, you can stop them in their tracks.

  1. Press Alt+F11 (for Mac ⌘+F11, or ⌘+Opt+F11) to open the Visual Basic Editor.
  2. Double click ThisWorkbook object to open the code window for the Workbook,
  3. Enter the following code
Sub Workbook_BeforePrint(Cancel As Boolean)
 Cancel = True
 MsgBox "Printing is not allowed for this workbook."
End Sub

Press Alt+Q (or ⌘+Q) when you’re done entering the code to save it and get back to Excel. Now each time users try to print from this workbook, nothing will happen. The MsgBox line of code is optional, but it’s always a good idea to include it to at least inform users so that they do not start hassling the IT department, saying there is a problem with their program!

Preventing Users from Printing a Particular Worksheet

If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead:

Private Sub workbook_BeforePrint(Cancel As Boolean)
 Select Case ActiveSheet.Name
        Case "Sheet1", "Sheet2"
         Cancel = True
         MsgBox "You cannot print this sheet"
 End Select
End Sub

Notice you’ve specified Sheet1 and Sheet2 as the only cases in which printing should be stopped. Of course, these can be the names of any sheets in your workbook; to add more sheets to the list, simply type a comma followed by the sheet name in quotation marks. If you need to prevent the printing of only one sheet, supply just that one name in quotes and drop the comma.

Visit this link to learn more about Workbook_BeforePrint event.

Understanding Workbooks and Worksheets: