Printing

[Guest]

Hi, Happy New Year to All

I use the code below that is placed in the ThisWorkbook to stop the printing
of certain sheets;

'Stops sheets printing as listed in Case
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Input", "sheet1", "sheet2", "sheet3", "sheet4"
Cancel = True
MsgBox "Sorry, you cannot print this page", vbInformation
End Select
End Sub

Is it possible to amend this to add an If statement to do the following;

Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now
print, but sheets 1 & 2 stay so they don't print?

Hope that makes sense.

Regards

Newbeetle

 

[Guest]

Hi,

You can do it all in the select statement checking for the true for the two
sheets.

I have introduced a variable cannotprint which is set to true if you cannot
print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
dim connotprint as boolean
Select Case ActiveSheet.Name
Case "Input", "sheet1", "sheet2"
cannotprint=true
case "sheet3", "sheet4"
cannotprint = worksheets("sheet1").range("E162")
case else
cannotprint=true
end select
if cannotprint then
Cancel = connotprint
MsgBox "Sorry, you cannot print this page", vbInformation
end if
End Sub

 

[Dave Peterson]

Just a warning...

The user can select multiple sheets and if one of those sheets isn't the
activesheet, that user can print what he/she wants.

 

[Guest]

Hi Martin,

I tried out the code but I'm having a few problems so I've done something
wrong. In case it was other item's in my workbook interfering I made a new
workbook and placed the following code in the VBA Thisworkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim connotprint As Boolean
Select Case ActiveSheet.Name
Case "sheet1", "sheet2"
cannotprint = True
Case "sheet3", "sheet4"
cannotprint = Worksheets("sheet1").Range("E1")
Case Else
cannotprint = True
End Select
If cannotprint Then
Cancel = connotprint
MsgBox "Sorry, you cannot print this page", vbInformation
End If
End Sub

I then put test text on the four sheets, when pressing the print button, the
message box appears for each sheet, but when I click the ok button the page
still prints.
I've tried a few things but really I'm a bit lost.