Prevent printing a sheet

[LindaC]

I have a workbook with 10 worksheets. On two of the worksheets only I want
users to be able to view them but I don't want them printed. Is there a way
to do this so only these 2 worksheets won't print. Thanks

 

[Gord Dibben]

BeforePrint code will prevent if users have enabled macros.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ActiveWindow.SelectedSheets
If sht.Name = "Sheet1" Or sht.Name = "Sheet2" Then
MsgBox "you are not allowed to print " & sht.Name
Cancel = True
End If
Next
End Sub

Note: if both are selected you will get the message box twice.......once for
each sheet.


Gord Dibben MS Excel MVP

 

[LindaC]

Thanks for the info. If I put this in the spreadsheet it works on all the
sheets, I only want to protect 2 of them from printing, thanks.

 

[Gord Dibben]

How so?

The code is to be placed in Thisworkbook module.

If Sheet1 and Sheet2 are not incuded in the selected sheets there will be no
msgbox and printing will be carried out.

Requires users to CTRL + Click or SHIFT + Click to select sheets to print.

Then you select File>Print>ActiveSheet(s)

Do not select "Entire workbook"


Gord