How to prevent users from printing worksheet?
In our daily work, there may be some important information in the worksheet, in order to protect the internal data outspread, the department will not allow us to print them. And here, I will talk about how to prevent users from printing worksheet.
Prevent users from printing one specified worksheet with VBA
Prevent users from printing the whole workbook with VBA
Prevent users from printing one specified worksheet with VBA
You can apply the following VBA code to protect your specific worksheet to be printed.
1. Activate your worksheet that you want to disable its print feature.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. And then in the left Project Explorer, double click ThisWorkbook to open the module, and paste the following code into it:
VBA code: prevent users from printing one specified worksheet
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Update 20140625
Dim WsName As String
WsName = "Sheet1"
For Each xWs In Application.ActiveWorkbook.Windows(1).SelectedSheets
If xWs.Name = WsName Then
MsgBox ("You can not print this worksheet")
Cancel = True
End If
Next
End Sub
Note: In the above code, the Sheet1 is the active worksheet which you are not allowed to print.
4. Then save and close this code, and now when you try to print this specific worksheet, you will get the following warning.
Note: With this code, the only specific worksheet is not allowed to print, but other sheets can be printed as usual.
Prevent users from printing the whole workbook with VBA
If you need to prevent users from printing the whole workbook, here also have a code can do you a favor.
1. Open your workbook which you don’t allow others to print.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. And then in the left Project Explorer, double click ThisWorkbook to open the module, and paste the following code into it:
VBA code: prevent users from printing one specified worksheet
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Update 20140626
Cancel = True
MsgBox "You can't print this workbook"
End Sub
4. Then save and close this code, and go to back the workbook, and now when you print one worksheet or the entire workbook, they will not allowed to be printed and you will get the following warning:
Related articles:
How to print multiple workbooks in Excel?
How to quickly print current page in Excel?
How to print long column on one page in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in