block printing of defined tabs

[joemeshuggah]

is it possible to program excel to prevent printing of specified tabs? i
have users that are too lazy to select the tab they want to print and are
printing tabs that have source data for pivot tables and wind up printing
pages upon pages that they don't need.

 

[Bernard Liengme]

Use this in a workBOOK module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Sheet1" Then
MsgBox "You may print only Sheet1"
Cancel = True
End If
End Sub

 

[joemeshuggah]

thanks...the block only appears to work if the user selects one of the tabs
that should not be printed. i believe the user is either selecting all tabs
or choosing "entire workbook" from the print menu...in which case all pages
print (even with the code below). is there an alternative to below that
would strictly allow a print of only sheets that i specifiy in the code?

 

[Bernard Liengme]

Played with this for a while with no luck
Some suggestions:
1) put a control on the OK-to-Print sheet(s) and train user to use this for
printing
2) hide the large worksheet, hidden worksheets do not print (check this!)
3) on the large worksheet, set the print area to a few cells in row 1. The
you will not waste time & paper. If you want to be wicked: in some unused
area of the large worksheet enter some text "Why the devil did you print
this!" and set the Print Area to that cell. This is called 'training' <grin>
best wishes