Matt
This event fires and runs before anything in the workbook is printed.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
The code between here and the End If only runs if TIME AND LEAVE is the
active sheet.
If ActiveSheet.Name = "TIME AND LEAVE" Then
This cancels the print action. It's usually used to control the printing
yourself. Delete this and the user will get the print dialog (assuming they
would get it otherwise)
Cancel = True
This stops other events from running. Namely, when you issue the PrintOut
command, you don't want this event to fire again. You don't "need" it
because we'll be deleting PrintOut, but it's not bad to have to increase the
speed of the macro.
Application.EnableEvents = False
This hides the actions from the user. It can also speed up execution of the
macro because Excel doesn't have to redraw the screen so much.
Application.ScreenUpdating = False
This starts a With Block. Any commands starting with a period relate to, in
this case, the ActiveSheet.
With ActiveSheet
This protects the sheet for the userinterface, which means you can change
things programmatically, but not in the UI
Worksheets("TIME AND LEAVE").Protect UserInterfaceOnly:=True
Changes the background color of the specified range. Same as Format -
Cells - Pattern in the UI.
.Range("A1:P40").Interior.ColorIndex = xlNone
Prints the sheet. Delete this so you get the dialog (by deleting Cancel
above) and so you don't get two printouts.
.PrintOut
Same as the one above, just on a more unwieldy range
..Range("A5:B5,C6:P9,O10:O11,M10:M11,K10:K11,I10:I 11,G10:G11,E10:E11,A10:C11,
C12:P12,O16,M16,K16,I16,G16,E16,A16:C16,A17:P33,O3 4:P40,A34:B40").Interior.C
olorIndex = 24
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.