before_print doesn't let to block printing

[zigulawrotka]

Hello,
It is my first post here, usually I'm able to sort out my issues with others posts -but this time i'm stuck. I'm just a beginner but trying my best to learn as much as I can.
I have recently moved from excel 2010 to 365. I was using macro in "This Workbook" to simply prevent worksheet from printing. Since I have moved to new excel macro does not prevent from printing. I know it fires because after I press print - I'm getting a message box from it but after I click OK it goes to print.

Option Explicit



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = True
    Debug.Print "Running Workbook_BeforePrint"


MsgBox "Printing Disabled"
Cancel = True


End Sub

I have tried with/without

Application.EnableEvents = True

also with/without

    Debug.Print "Running Workbook_BeforePrint"

Each time I get message box - click ok and it goes to print. If you have any ideas in what I could check please let me know - it will be much appreciated.
Thanking you in advance!

 

[Logit]

.
Try placing the Cancel command as the first line :

Option Explicit


Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "You can't print this workbook"
End Sub

 

[zigulawrotka]

thanks Logit,
I have just tried your suggestion, unfortunately result is the same - still prints out.

 

[Yongle]

The code is good so the issue is probably in the workbook
Prove that by creating new workbook and using @Logit solution

 

[zigulawrotka]

Hi Yongle,

I have created a new workbook, made up some entries so there is something to print and macro as per @Logit solution.
But it is still not working, message box appeared and it went to print.
I have even tried to loop it until (just an example)B2 cell gets a value though
an input box but all it does when value isn’t there it hangs and then all I can do is restart excel

 

[Yongle]

The code works for me on Excel 365

You seem to have a minor corruption
- no idea how to fix
- (because it is so quick and it clears other unknown issues that are building) I simply re-install with problems like this and that usually does the job


In the meantime here is an alternative method to frustrate printing
- a useless print is generated

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim w As Worksheet
    For Each w In ThisWorkbook.Worksheets
        w.Activate
        w.PageSetup.PrintArea = w.Cells(Rows.Count - 1, Columns.Count).Resize(2).Address
        Spoil
    Next
    ThisWorkbook.Save
    MsgBox "You can't print this workbook"
End Sub

Private Sub Spoil()
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        .LeftHeader = "Why are you trying to print this?"
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .PrintHeadings = False
    End With
End Sub

OR possibly ...

Private Sub Workbook_BeforePrint(Cancel As Boolean)    
    MsgBox "You can't print this workbook and it will close"
    ThisWorkbook.Close True
End Sub

 

[zigulawrotka]

Thank you, this is a perfect solution(s) for now. I guess if code works for you it must be something wrong in my excel - will work on this

 

[Yongle]

Thanks for feedback