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

Hi Paul,

It is not clear to me why you need to
differntiate between the user's selection
of the PrintPreview option and the Print
option; surely, the format actions should
be identical?

Re-phrasing my question: what different,
or additional action should be envisaged
if the user selects the preview option?




---
Regards.
Norman


"Paul D Byrne." wrote in message
...
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

Hi Norman - thanks for the reply,

The formatting options are identical, however I cannot get the correct
method to fire off when their respective button is pushed. eg

If I use "ActiveWindow.SelectedSheets.PrintPreview" then it previews every
time - even when the Print button or File / Print menu option is selected.

If instead, I use "Application.Dialogs(xlDialogPrint).Show" I get the Print
dialog box every time - even when the Print Preview button or command is
executed.

hope this clears things up.

cheers,

Paul B.
"Norman Jones" wrote:

Hi Paul,

It is not clear to me why you need to
differntiate between the user's selection
of the PrintPreview option and the Print
option; surely, the format actions should
be identical?

Re-phrasing my question: what different,
or additional action should be envisaged
if the user selects the preview option?




---
Regards.
Norman


"Paul D Byrne." wrote in message
...
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