BeforePrint event - Jim Thomlinson / pwrichcreek

[Paul D Byrne.]

Hi,

Read the post re 'Get Control Before AND After Printing' and it is exactly
what I am also trying to do - however, I want to differentiate between the
user clicking on Print and on Print Preview. I read somewhere that this isn't
possible - is this right?

Basically the only thing I want to happen when a user prresses either print
or print preivew is the black shading turned to white and the white letters
turned to black on the header and then back again after the event is
finished.

Below is the code I have used so far.

The limitation is I can get the Print Preview or the Print Dialog but not
the print dialog when print is pressed and the print preview when print
preview is pressed.

thanks,

Paul B.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim shtTab As Worksheet
Dim shtStart As Worksheet
Dim rngSelection As Range

On Error GoTo Workbook_BeforePrint_Error

Set shtStart = ActiveSheet
Application.EnableEvents = False
Cancel = True

For Each shtTab In ActiveWorkbook.Sheets

Cells(1, 1).Select

Select Case shtTab.Name

'Sheets I don't want anything to happen to
Case "Summary", "DataHandler"

'Sheets I do
Case Else

With shtTab.Range("1:5")

.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic

End With

End Select

Next

'this is the part that doesn't differentiate between Print and Print Preview
ActiveWindow.SelectedSheets.PrintPreview

'OR

Application.Dialogs(xlDialogPrint).Show

For Each shtTab In ActiveWorkbook.Sheets

Cells(1, 1).Select

Select Case shtTab.Name

Case "FFX Check"
Set rngSelection = shtTab.Range("A1:H5")
GoSub ColourSelection

'there are more sheets but this is the gist of it.
End Select

Next

Workbook_BeforePrint_Exit:
Application.EnableEvents = True
shtStart.Activate
Exit Sub


Workbook_BeforePrint_Error:
MsgBox "Error in Print : " & Err.Number _
& vbNewLine & "Error Description : " & Err.Description
Resume Workbook_BeforePrint_Exit

'----------GoSub Procedures---------------------
ColourSelection:
rngSelection.Interior.ColorIndex = 1
rngSelection.Interior.Pattern = xlSolid
rngSelection.Font.ColorIndex = 2
Return

End Sub