stopping a sheet from printing

[Acehole]

hello people who know lots of stuff,
how do i stop a sheet from being printed and add an error message/ dialogue box saying this sheet cannot be printed, view only etc etc.
this to appear when they goto the print option in the menus.
thanks in advance.
Acehole

 

[Acehole]

hello, i have managed to find this code, however it stops all of the sheets being printed, i require only selected sheets not to be printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Printing of these pages is not permitted ", vbInformation, "COMPUTER SAYS NO"
Cancel = True
End Sub

thanks
Acehole

 

[Acehole]

apoogies for the bump,
can this actually be done?
Ace

 

[Acehole]

i'm shocked at you guys, normally get a response from someone,
any reason why not? even if it is abuse for being so poor ar VBA,
Ace.

 

[gaj104]

i'm shocked at you guys

Hoping this is in jest, and that you dont treat this as a IT helpdesk rather than a learning resource.

Anyways, code below does the job and handles multiple sheets

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim bln As Boolean

bln = InRange

If bln = True Then
    MsgBox "Printing of these pages is not permitted ", vbInformation, "COMPUTER SAYS NO"
    Cancel = InRange
End If


End Sub

Function InRange() As Boolean

Dim ws As Variant
Dim i As Long

Set ws = ActiveWindow.SelectedSheets
InRange = False

For i = 1 To ws.Count

    Select Case ws(i).Name
        Case "Sheet1", "Sheet2" 'etc etc
            InRange = True
    End Select

Next i

End Function

Hoep it helps

 

[Acehole]

thank you for that worked spot on, as i'm sure you knew it would.
now to find out what all the terms in the code mean.
thanks again
Ace.