Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to print w/out cell shading
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("A1:P40").Interior.ColorIndex = xlNon ..PrintOu ..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,O34:P40,A34:B40").Interio r.ColorIndex = 2 End Wit Application.EnableEvents = Tru Application.ScreenUpdating = Tru End I End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to print w/out cell shading
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to print w/out cell shading
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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Dick Kusleika" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to print w/out cell shading
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'? -- David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to print w/out cell shading
Brilliant.
Dick "David" wrote in message ... 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'? -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot see cell shading on the screen, only in print preview | Excel Discussion (Misc queries) | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Why do the colors or shading only show up in print preview in Exce | New Users to Excel | |||
My shading is hidden. I can only view it in print preiview. | Excel Discussion (Misc queries) | |||
When shading cells using the 'pattern' option, they print in grey. | Excel Discussion (Misc queries) |