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:
- Click the
Filetab and then
- Click on
Protect Workbookand then
- In the
Permissiondialog box, click on
- Uncheck the
Print contentoption and
- Click OK.
By doing this, you will disable the Print command and the Print Preview feature for the workbook.
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.
⌘+Opt+F11) to open the Visual Basic Editor.
- Double click
ThisWorkbookobject to open the code window for the Workbook,
- Enter the following code
Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "Printing is not allowed for this workbook." End Sub
⌘+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
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: