VBA or MACRO to prevent someone from printing the contents of an Excel worksheet

  • Thread starter Thread starter Marcello do Guzman
  • Start date Start date
M

Marcello do Guzman

Hi,

Does anyone know the macro or VBA code to prevent users from printing
a worksheet. They can edit, view, but they CANNOT print the contents
of a worksheet. Please explain exactly what I need to do where I
place the code and so forth. Your help with this would be greatly
appreciated. Please respond via email to:

(e-mail address removed)

Thanks for any help you may give.
 
Place the following first two macros in the WORKSHEET you want to avoid someone from printing
Place the third macro below into a Module

Basically they does the following things
On activate of a worksheet
1) Disable the "Print" menu ite
2) Replace the Excel shortcut key Ctrl+p with a dummy macro (which contains nothing)
On deactivate of the worksheet
3) Reverse (1) and (2)

'-----Put in the worksheet-----------------------
Private Sub Worksheet_Activate(
Application.CommandBars(1).Controls("File").Controls("Print...").Enabled = Fals
Application.OnKey "^p", "dummy_macro
End Su

Private Sub Worksheet_Deactivate(
Application.CommandBars(1).Controls("File").Controls("Print...").Enabled = Tru
Application.OnKey "^p
End Su
'-----------------------------------------------------

'-----Put in a module-------------------------------
Private Sub dummy_macro(

End Su
'-----------------------------------------------------


Regards
Edwin Ta
(e-mail address removed)
 
Marcello,

In the ThisWorkbook code module, use

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Edwin and Chip have given ways to prevent printing. But the user may
open the workbook with macros disabled. So you need to make it useless
without macros, for example, have a Worksheet_Open show the useful
sheet, and hide a dummy sheet that reminds the user to enable macros
when opening the sheet.

You'll also have to deal with disabling cut/copy, so they can't paste
the results into another workbook, and remember to prevent saving, while
you're at it. Then protect the code in the VBE, and hope they really
don't care much about breaking in.

- Jon
 
Back
Top