Remove shading just for printing only

Topic

New Reply

Hi to all,

I am trying to find a way to take a spreadsheet and through VBA, when ready to print the sheet out I want all of the cells with Shading to simply print out without the shading, but retains the shading in the worksheets in the end. I had rigged up a way to make it remove the shading with the macro, and make it print without the shading, but afterwards the shading was removed from the worksheet. Simply I have it to select the entire worksheet, then go to Home and in the Fills box use the “no fills” selection, then use the print command and after click “undo” to return the sheet to it’s prior shaded state. Only, the macro would not use the “undo” action. I am no Excel guru so I am needing help in making this work. And I forgot one thing, I wanted this to be activated with a Command Button to be used when ready to print, which I had used one already.

Thanks in advance for any assistance,
BudE :huh:

Reply | Quote

Replies

One method would be to create a copy of the sheet you want to print, remove the shading from that sheet and print it, then delete the unwanted sheet. Substitute Sheet1 with the name of the sheet you’re working on in the following:

Code:

Sub PrintWithoutFormats()
    Sheets(“Sheet1”).Select
    Sheets(“Sheet1”).Copy Before:=Sheets(1)
    Sheets(“Sheet1 (2)”).Select
    Cells.Select
    Selection.ClearFormats
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWindow.SelectedSheets.Delete
End Sub

You then have to click on Delete to remove the unwanted sheets, but someone may know a way to autoconfirm the deletion which would make it smoother.

Reply | Quote

One method would be to create a copy of the sheet you want to print, remove the shading from that sheet and print it, then delete the unwanted sheet. Substitute Sheet1 with the name of the sheet you’re working on in the following:

Code:

Sub PrintWithoutFormats()
    Sheets(“Sheet1”).Select
    Sheets(“Sheet1”).Copy Before:=Sheets(1)
    Sheets(“Sheet1 (2)”).Select
    Cells.Select
    Selection.ClearFormats
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWindow.SelectedSheets.Delete
End Sub

You then have to click on Delete to remove the unwanted sheets, but someone may know a way to autoconfirm the deletion which would make it smoother.

Hi again and thank you for your help. I placed this code in a new worksheet, on sheet1 and the code hung and stated: “IgnorePrintAreas:=False ” Not sure what that is but it didn’t let the code work.

Reply | Quote

Hi BudE

..welcome to the lounge.

Try this:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.BlackAndWhite = True
End Sub

No need to have a command button.

zeddy

Reply | Quote

Hi BudE

..welcome to the lounge.

Try this:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.BlackAndWhite = True
End Sub

No need to have a command button.

zeddy

And thank you too zeddy. I placed the code in the VBE but not sure what to do to make it work.How do I use this code? How to activate it or whatever?

thanks

Reply | Quote

Hi BudE

The code must be placed in the ThisWorkbook section, not in a module.
In the VBE, display the Project Explorer ( [Ctrl][R] ) in the left-hand pane of the VBE.
Double-click the ThisWorkbook , then in the right-pane, paste the routine.

This code is an event-driven code. As the routine name suggests, whenever you do a print (or print-preview), this code will be automatically executed. So you don’t need a button.

zeddy

Reply | Quote

As an alternate to Zeddys code, you could use unclehewie” code with a few modifications, You do not want to remove all formatting such as bold. As I understand it that you want to remove just the row shading. I made those adjustments along with inhibiting any alerts when deleting the sheet. It is also generic in that you do not have to worry about sheet names

Place in a standard module

Code:

Sub PrintWithoutFormats()
    Application.ScreenUpdating = False
    ActiveSheet.Copy Before:=Sheets(1)
    Cells.Interior.Pattern = xlNone
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
End Sub

I would use Zeddy’s because it is the easiest approach

Reply | Quote

Is this ROW shading? If so, how did it get there? If conditional formatting just record a macro applying the CF and removing the CF. Now, change it to remove the CF>print>replace CF.

Reply | Quote

Zeddy’s snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie’s approach might prove worthy.

Reply | Quote

Zeddy’s snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie’s approach might prove worthy.

Wow, just like magic. I used Zeddy’s version and it worked like magic. Thank you Zeddy and everyone that offered help on this. My head is so sore from me banging it on the desk trying to find a solution. And yes, just the In Cell Shading is what I was after. They send the work in from outside and for some reason add shading…which might work for them. However, when they fax it in to me it comes out as a Black area and not able to read. Countless efforts have been made to get them to not do that. And to think you did a very simple small bit of code to work the magic. Now to get all of the station Sups to put this in their worksheets.

Have a terrific day and thanks a million.
This site “ROCKS”
Bud

Reply | Quote

Zeddy’s snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie’s approach might prove worthy.

Hey, back one more time. Something I didn’t think of when doing this. The code works fine and does just what it should, but the lack of thought on my part was, when they need for the shading to print out for their own personal reasons, whatever they might be, how could it be set to print with the shading? That was my over looking things. So to print out to fax to me, removing the shading works, but to allow them to print the shading for themselves…maybe for some kind of reports or something what could I do to allow that just for them?

BudE

Reply | Quote

Put is a checkbox and if checked apply the macro, if not, don’t

Reply | Quote

Hi to all,

.. and after click “undo” to return the sheet to it’s prior shaded state. Only, the macro would not use the “undo” action. …

BudE :huh:

Hi BudE

How about saving the worksheet before printing. Remove the shading and then print and close without saving.

This locks in all the changes plus the shading. You can use something like:

ThisWorkbook.Close savechanges:=False

And this will close the workbook without saving it thus preserving the state it was before the printing.

Please let us know if this solved your issue.

Wassim

Reply | Quote

Hi there. If you notice what Zeddy offered, that is what I used and that part works great. Only thing was at other times I want the shading to actually print for the originator when they need it to, for presentation purposes or whatever they do with it. What it is actually is they print out these worksheets and then scan and fax to me and others. Only, when you have the shading it would come out black when we received it. Only needed for it to send it to us without the shading. But I left out the part I still want them (originator or sender) to be able to print out the shading on their end when they want to. That was the part I left out originally when I posted here and is the last bit I need to figure out. Oh, and I’m not sure if I want the worksheet to close after they print it out to send to us. I’m trying not to disturb how they work in their offices so as to not cause any friction. Any way to make it work both ways?

Thanks,
Bud

Reply | Quote

Hi Bud

Change my original to this:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
saywhat = "Print for fax (no shading)?"
btns = vbYesNo + vbDefaultButton2 + vbQuestion
boxtitle = "Print setup"
answer = MsgBox(saywhat, btns, boxtitle)
If answer = vbYes Then
ActiveSheet.PageSetup.BlackAndWhite = True
Else
ActiveSheet.PageSetup.BlackAndWhite = False
End If
End Sub

zeddy

Reply | Quote

Hi Bud

Change my original to this:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
saywhat = "Print for fax (no shading)?"
btns = vbYesNo + vbDefaultButton2 + vbQuestion
boxtitle = "Print setup"
answer = MsgBox(saywhat, btns, boxtitle)
If answer = vbYes Then
ActiveSheet.PageSetup.BlackAndWhite = True
Else
ActiveSheet.PageSetup.BlackAndWhite = False
End If
End Sub

zeddy

Thanks a million zeddy. That worked so beautifully. You are a genius……
Thanks again
BudE

Reply | Quote

The only problem I see with this is that it will fire each time you do a print preview so you may want to use a checkbox approach instead.

Reply | Quote

Hi Don

..you are correct, to be prompted every time could get annoying.
However, if you have many sheets, you’d need to remember where you put your own checkbox.
In which case, perhaps it’s easier to use the checkbox that is already available on the [Sheet] tab of the [Page Setup] dialog sheet [ ]Black and white. But then, if you forget to use the [Page Setup] you might not get the printout you want, and then have to redo it again..
So, whatever you prefer I guess.

zeddy

Reply | Quote

A seamless method may be to take Zeddy’s original code out of the Workbook_BeforePrint event and place it in a standard module. Then assign it a keyboard shortcut. The users print in the normal fashion but you can print without the shading using the shortcut.

Code:

Public Sub PrintBandW()
ActiveSheet.PageSetup.BlackAndWhite = True
ActiveWindow.SelectedSheets.PrintOut
End Sub

Nicole

Reply | Quote

Hi Nicole

let me be the first to properly thank you, for showing another method.
May I suggest [Ctrl][Shift][Z]?
(I love zzzzz’s)

zeddy

Reply | Quote

I was a wee bit nervous posting because I thought it might be taken as an insult. The coding was from an internet search I did a while back. My first Thank You. You made my day Zeddy!!!

Reply | Quote