Code to print w/out cell shading

[Matt]

I have this code that removes the cell shading then prints the sheet and then places the cell shading back. However, when this code runs, the user does not get a Print dialog box so they can choose how many copies or which printer to use. I would like to modify this code so that they get the Print dailog box. How should I modify this code or is there a better way to do this? Also, if someone could explain to me what this code is doing, that would be great. Thanks in advance. Mat

Private Sub Workbook_BeforePrint(Cancel As Boolean
If ActiveSheet.Name = "TIME AND LEAVE" The
Cancel = Tru
Application.EnableEvents = Fals
Application.ScreenUpdating = Fals
With ActiveShee
Worksheets("TIME AND LEAVE").Protect UserInterfaceOnly:=Tru
..Range("A140").Interior.ColorIndex = xlNon
..PrintOu
..Range("A5:B5,C69,O10:O11,M10:M11,K10:K11,I10:I11,G10:G11,E10:E11,A10:C11,C1212,O16,M16,K16,I16,G16,E16,A16:C16,A1733,O3440,A34:B40").Interior.ColorIndex = 2
End Wit
Application.EnableEvents = Tru
Application.ScreenUpdating = Tru
End I
End Sub

 

[Dick Kusleika]

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("A140").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,C69,O10:O11,M10:M11,K10:K11,I10:I11,G10:G11,E10:E11,A10:C11,
C1212,O16,M16,K16,I16,G16,E16,A16:C16,A1733,O3440,A34:B40").Interior.C
olorIndex = 24

 

[Dick Kusleika]

Matt

Forget what I said about deleting the Cancel and PrintOut lines. You need
to control the printing, because you want to restore the cell shading after
it prints. With my changes, it would delete the cell shading, then restore
it, then print. Not exactly a useful macro. Instead, just replace

..PrintOut

with

Application.Dialogs(xlDialogPrint).Show

and you'll get the functionality that you want.

 

[David]

I know I'm jumping in late here and didn't see the original post, but the
Subject: makes me ask why can't the user just set Page Setup|Sheet|Print
option to 'Black and white'?

 

[Dick Kusleika]

Brilliant.

Dick