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

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

On Tue, 12 Jan 2010 11:09:01 -0800, LindaC
wrote:

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

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" wrote:

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

On Tue, 12 Jan 2010 11:09:01 -0800, LindaC
wrote:

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

.

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 FilePrintActiveSheet(s)

Do not select "Entire workbook"


Gord


On Wed, 13 Jan 2010 08:36:01 -0800, LindaC
wrote:

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" wrote:

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

On Tue, 12 Jan 2010 11:09:01 -0800, LindaC
wrote:

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

.