How can I prevent others printing a worksheet

[Guest]

I want to allow others to view a workbook as read only (which is not a problem) but I can't work out how to prevent them from printing it.

I still need to print it myself.

Many thanks for your help.

Bazil

 

[Mark O'Brien]

Using VBA you can do this. On the worksheet object you could put this code in :

<pre>
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

</pre>

This is not a perfect solution, but it does stop them from printing.

 

[Guest]

This is a fairly "simple" solution which doesn't use macros, but it works fine.

Put your cursor in cell A1 - make sure that row 1 is empty as you will be hiding it later. Goto File, Print Area, Set print Area. Select row 1 and hide it, then protect the sheet. If someone tries to print it will print a blank page as the print area does not contain any info. You hide row A to prevent anyone moving the page breaks (which you can do even if the sheet has been protected), but you cannot unhide a row if the sheet has been protected.

 

[lenze]

Try this code in the WorkBook Module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Input box to verify password

Dim myPassword As String

myPassword = InputBox(prompt:="Please enter the password to proceed:", _
Title:="Password is required to print this file.")

If myPassword <> "Password" Then
MsgBox prompt:="Click OK to return to Report.", _
Title:="Cancelled -- correct password not entered", _
Buttons:=16
Cancel = True

Else
End If
End Sub

Change "Password" as desired

 

[Guest]

Many thanks for the quick replies - excellent stuff.

Bazil