Software

How to keep others from printing an Excel worksheet

Use a simple macro to inhibit printing in an Excel workbook.

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"
End Sub

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:

  1. Press Alt + F11 to open the Visual Basic Editor (VBE).
  2. Display the Project Explorer (if necessary) by pressing Ctrl + R.
  3. Select This Workbook to launch the worksheet's module.
  4. 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:

july2008blog4fig1.jpg

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.

About

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.

5 comments
Kristy83
Kristy83

I tried this and it won't print the sheet which is great but it is no longer using the formulas on the sheet they will not add up please help????? :-(

zimonar
zimonar

Done, I try it; it is a very nice idea to keep others from printing my worksheet. But the problem is, that since I try it, I even, can???t print any worksheet else, not only my worksheet. Please, can you tell me how to fix this problem? Thanks :)

zimonar
zimonar

Done, I try it; it is a very nice idea to keep others from printing my worksheet. But the problem is, that since I try it, I even, can?t print any worksheet else, not only my worksheet. Please, can you tell me how to fix this problem? Thanks :)

maharshik
maharshik

You can restrict printing only to your nt login. Private Sub Workbook_BeforePrint(Cancel As Boolean) If Environ("USERNAME") = "your_windows_network_login" Then Cancel = False Else Cancel = True MsgBox "You can't print this worksheet", vbOKOnly, "Error" End If End Sub Ofcourse there is a tweak for this also :-) Enjoy, Hari Krishna

NickNielsen
NickNielsen

Go to Tools | Macro, select your macro and disable it.

Editor's Picks