Prevent printing of some sheets and not others in a single doc?
 

I need to disable the Print command in 11 out of 15 sheets (tabs) in a single
document. I have the VB script for disabling printing for the entire document
but I would like to know if it is possible the disable printing on selected
sheets.
Thanks...

Prevent printing of some sheets and not others in a single doc?
 

I don't think you can.

The user can always choose "whole document" or group sheets before printing and
you won't know what sheets are being printed.

If the user were always just printing the activesheet, you could stop them:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim mySheetNames As Variant

mySheetNames = Array("sheet1", "sheet2", "sheet3")

If IsNumeric(Application.Match(ActiveSheet.Name, mySheetNames, 0)) Then
Cancel = True
MsgBox "Nope!"
End If

End Sub

Just keep adding all the sheets you don't want to print to that array.

This goes under the ThisWorkbook module.

But it sure ain't perfect--disabling macros/events would allow printing, too!

kblake wrote:

I need to disable the Print command in 11 out of 15 sheets (tabs) in a single
document. I have the VB script for disabling printing for the entire document
but I would like to know if it is possible the disable printing on selected
sheets.
Thanks...

--

Dave Peterson

Prevent printing of some sheets and not others in a single doc?
 

kblake,
How about:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim WS As Worksheet

For Each WS In ActiveWindow.SelectedSheets
If WS.Name = "Banned Sheet" Then
MsgBox "Printing of " & WS.Name & " is not allowed."
Cancel = True
Exit Sub
End If
Next

End Sub

Or use Application level events for the same.
However this also PrintPreview, which maybe a problem.

NickHK

"kblake" wrote in message
...
I need to disable the Print command in 11 out of 15 sheets (tabs) in a
single
document. I have the VB script for disabling printing for the entire
document
but I would like to know if it is possible the disable printing on
selected
sheets.
Thanks...