Solved: Prevent background color from printing

[brucemc]

Is there some code I can stick in (VBA) that would prevent (turn off) any of the background colors from printing when a user clicks a print button (macro)? I want to use a dark color for screen, but having a black page print out is not too helpful. I guess I could change the screen update, background color to none, print out, then reset the back ground, but where there might be varied light background colors (that already are near clear or light grey at worst in print) it will become a pain...

[OBP]

Why not have a Printing Sheet, all you need then is a very simple macro to copy the Screen Sheet and paste Special Values to the Printing sheet, print it and clear it. This can all be done by recording macro as you do it.

[Wizard]

This line in your VBA will make it print out just B&W:
[VBA] ActiveSheet.PageSetup.BlackAndWhite = True[/VBA]

There are a lot of other printing controls that you can add, including headers, footers, etc etc - the easiest way is to start a macro recording & go thru the File menu to Page Setup, & work that till it's the way you like it. You'll end up with a full set of options in a 'With... End With' group.

[brucemc]

Folks, THANK-YOU.

I really thought that the only thing would happen with the BlackAndWhite property would be the darker color backgrounds would come in as one heck of a lot of black toner! Should I switch it back to False at the end of the routine or is it one of those that only effect the current operation?

[Wizard]

No problem.

I think you will find that that is actually sheet-specific - i.e. if that is set to print B&W, the other sheets will still print in glorious colour, unless you tell them differently.

As far as thinking it would behave differently, I can only say Experiment. You can't really break anything (especially if you're in an experimental copy of your spreadsheet), and it's frequently a faster way to get your answer than hunting on the 'net.

[Ken Puls]

Should I switch it back to False at the end of the routine...?

Yes.

[geekgirlau]

Hi Bruce,

I've marked this thread as Solved for you - normally you can use the "Thread Tools" option at the top of the screen, but it's not working at present.