Print Selected Sheets in Black and White

[delaing]

I did quite a bit of searching around for a solution on this and have not hit on one that seems to satisfy.

For some reason, Excel seems to ignore the Printer Properties if the individual worksheet is NOT set to print in B&W. If one of my sheets has the Page Setup/Sheet/Print black and white box unchecked, then it will print the font colors, shading, etc. on the worksheet even though the Printer Properties are setup for B&W.

From what I've read, VBA cannot affect the Printer Properties from within Excel. But, if I can figure out how to set the Page Setup to B&W for the selected sheets, that seems to force it B&W at Print time.

Here is the snippet of code where the sheets are selected and prepped for printing:

Please Login or Register  to view this content.

Is there a way that I can modify the With section to affect the Page Setup or does this need to happen prior to this and how?

Thank you,
Delain

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[delaing]

This worked great, thanks for the quick response.

How could we change this so I tie it to a Workbook Open event and have the PageSetup.BlackAndWhite = True be applied to all sheets of the workbook?
This would help when the user wants to print a single sheet or group of sheets. I can't figure out the Object to connect this property to in this scenario.

Thank you,
Delain

[AlphaFrog]

Please Login or Register  to view this content.

[delaing]

And this will work for any workbook that the user opens? Or is this event restricted to use on a per-workbook only basis?

I don't use too many Event procedures yet, so not well-versed.

Thanks for your great help on this.

Delain

[delaing]

AF, I think I figured it out.
Simply took your last bit of code and put it in the Print routine as:

Please Login or Register  to view this content.

This way, it changes the setting for all of the sheets when this Print routine is run; regardless, of sheets being selected.

I was, though, looking for a way to change the setting for all sheets within when any workbook is opened and the user simply does a Ctrl-P to print a sheet.

Thank you,
Delain