BeforePrint event - Jim Thomlinson / pwrichcreek
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
|