Set Print Area based on cell value 1

[waubain]

I am trying to set the print area based on the value of a cell on the ActiveSheet. This seemed fairly straight forward, but it still wants to print the entire Worksheet. I am using Excell 2007. Thanks for any help.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False

    If Worksheets("CSP565").Range("R13").Value = 1 Then
        ActiveSheet.PageSetup.PrintArea = "$A$1:$O$35"
        Else: ActiveSheet.PageSetup.PrintArea = "$A$1:$O$69"
    End If
Application.EnableEvents = True
End Sub

You don't know what you don't know...

 

[SkipVought]

hi,

Else: ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

Hi Skip,
Thanks for the prompt response, but the entire WorkSheet (4 pages) still prints rather than the first or second page depending on the value of cell (R13). It almost seems that Workbook_BeforePrint(Cancel As Boolean) is not being recognized. Is there a way to test this, I tried
this but :

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    MsgBox "Hi!"

End Sub

You don't know what you don't know...

 

[SkipVought]

So are you saying that it is not executing the THEN, rather the ELSE?

Have you enabled Macros? Did you issue a PRINT command?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

I change the sub to run from a cmdButton rather than the Workbook_BeforePrint and entered a ActiveSheet.Printout at the end and everything is now working at it should. Thanks for the hints.
Bob

You don't know what you don't know...