Workbook_BeforePrint 1

[johnny45]

Trying to delete a range if the user prints a particular sheet, however I do NOT want to delete the range if the print preview is clicked...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.CodeName = "Sheet6" Then
Application.EnableEvents = False
Cancel = True
Sheet6.PrintOut
Sheet6.Range("A1:I1,B2,B3,H2,H3,B12:H28,H30,H32,H33,H35").ClearContents
Application.EnableEvents = True
end if
End Sub

1-The above code gives a Method Range of oject _worksheet failed when trying to clear the range, how can I correct this ?

2- How can I NOT clear the Range if the user clicks print Preview from sheet6 ?

 

[SkipVought]

Not a good range syntax...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.CodeName = "Sheet6" Then
        Application.EnableEvents = False
        Cancel = True
        With sheet6
            .PrintOut
            Union(.[A1:I1], .[B2:B3], .[H2:H3], .[B12:H28], .[H30], .[H32:H33], .[H35]).ClearContents
        End With
        Application.EnableEvents = True
    End If
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[johnny45]

Thank you for correcting my range syntax..
How about the 2 part to my question,
If he user hits the printpreview, how can I not delete the range ?

 

[SkipVought]

There is no BeforePrint event when PrintPreview is envoked.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[johnny45]

I'm using Excel 2003,
If I click the print preview icon then close the print preview my range gets deleted..

 

[SkipVought]

Interesting that the PrintPreview envokes the Workbook_BeforePrint event. I did not expect to see that.

Got to think about this one.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}