Unable to Disable Worksheet Printing

[whereswayno]

This is very frustrating. I am using a Windows 7 machine with Excel 2010. I want to cancel printing a worksheet if there are too many pages. Thus I understand that I need to have code as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If PageSetup.Pages > 2 then
Cancel = True
MsgBox "Please change the print area into ONLY 2 pages.", vbInformation
End If
End Sub

However, none of this code is being run when the print icon is pressed or when someone clicks File -> Print -> Print. I went to the trust center and verified that all macros are enabled and that access to the VBA project module was trusted too. I verified that I saved the project before trying to click the print icon.

In case my code was incorrect, I simplified it:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Please change the print area into ONLY 2 pages.", vbInformation
End Sub

I never received this message, so none of it ran.

Does anyone have any ideas how to fix this?

 

[wigi]

Are the events enabled? Run once:

Application.EnableEvents = True

Also, where did you paste the code?

 

[whereswayno]

I added your code as the first line inside the Sub, but that did not change the result. I put my code in Sheet2 (once I can get this code to work in this sheet, I'll be putting similar code in the other sheets).

 

[shg]

Private Sub [COLOR=red][B]Workbook[/B][/COLOR]_BeforePrint

It's a workbook event; it needs to go in ThisWorkbook.

 

[whereswayno]

Ok, I've made that change, but it still doesn't run.

 

[shg]

Try this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.PageSetup.Pages.Count > 2 Then
        MsgBox "Please change the print area into ONLY 2 pages.", vbInformation
        Cancel = True
    Else
        MsgBox "Okey-dokey"
    End If
End Sub

If nothing happens, make sure that the code is in the ThisWorkbook module, macros are enabled, and that

? Application.EnableEvents

entered in the Immediate window returns True.