Cancel printing a worksheet

[Johnny W.]

Having a similar problem where I never wanted a selected sheet to
print. The solutions posted at best still printed something when the
sheet that I did not want to print was active. I used the following
code to alway block the printing of "Sheet1" in the example where the
workbook has three (3) sheets:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
MsgBox "Print Error, Unable to Print Sheet1", vbCritical
Cancel = True
Else
If ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.Worksheets("Sheet1").Visible = xlVeryHidden
Application.Dialogs(xlDialogPrint).Show
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible
Application.DisplayAlerts = True
Application.EnableEvents = True
Cancel = True
End If
End If
End Sub

The down side of this solution is that one must list all other
workbook sheets in the line following the "Else" statement. The
"Cancel" command near the end of the code was necessary to prevent two
(2) sheets from printing.

 

[Dave Peterson]

That really doesn't stop them from printing sheet1. They can have Sheet2 active
and print the entire workbook.

If this were important to me, then I'd turn off all printing with code like
this:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Please use the button to print"
End Sub

Then give the users a way to print -- but only what you want to allow.

Option Explicit
Sub YourPrintMacroHere()

if lcase(activesheet.codename) = lcase("Sheet1") then
msgbox "can't be printed"
exit sub
end if

'right before you print
application.enableevents = false
activesheet.printout
application.enableevents = true

End Sub

Then put a button from the Forms toolbar on each sheet that's ok to print. And
assign this macro to each button.

===

Instead of relying on the sheet name on the tab (which is easier for the user to
change), I used the codename for the sheet--which is more difficult for the
typical user.

But all this dies if the user disables macros!