block printing of defined tabs
 

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.

block printing of defined tabs
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"joemeshuggah" wrote in message
...
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.

block printing of defined tabs
 

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

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"joemeshuggah" wrote in message
...
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.

block printing of defined tabs
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"joemeshuggah" wrote in message
...
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" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"joemeshuggah" wrote in message
...
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.