It's common to share a worksheet with others, but for many reasons you might not want them to print it. Perhaps you're concerned about data theft or security. Maybe the data is confidential and you don't want to risk a worksheet lying around on someone's desk. Whatever your reason, Excel doesn't offer a built-in feature that inhibits a print request.
The simplest way to inhibit printing of a worksheet is to cancel the print job before it starts using the following macro:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "You can't print this worksheet", vbOKOnly, "Error"
If someone tries to print the worksheet by any means, this macro automatically cancels the request. All print options are still visible and available, they just appear not to work. That might be a bit confusing, hence the MsgBox function. A message isn't strictly necessary, but it certainly is helpful.
To create the macro:
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Display the Project Explorer (if necessary) by pressing Ctrl + R.
- Select This Workbook to launch the worksheet's module.
- Enter the macro as shown above.
After entering the macro, return to the worksheet and try to print it. Excel will display the following message:
Depending on your needs, you might also refer the user to a particular person by providing an in-house phone number or e-mail address if they need help. Of course, this won't stop the truly savvy user from printing the worksheet. Anyone who knows just a little about Excel will know how to bypass a macro, so this isn't a powerful security measure.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.