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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
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,
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
 
Back
Top