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
|
Similar Threads
