Forcing Black & White printout with SelectedSheets.Print

javajoe

Board Regular
Joined
Nov 7, 2005
Messages
78
Anyone know how to force a printout in Black & White if the default printer is color and the setting is set to Automatic?

I have this string but would like to force B&W printing with this workbook:

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1

Thanks!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Here's something I've used in the past (note the BlackandWhite property):

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = "$B:$E"
        .CenterHeader = _
            Sheets("Color Summary").Range("C1") & " " & _
            Sheets("Color Summary").Range("D1") & " Color Summary"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .CenterHorizontally = <SPAN style="color:#00007F">True</SPAN>
        .CenterVertically = <SPAN style="color:#00007F">False</SPAN>
        .Orientation = xlLandscape
        .Order = xlDownThenOver
        
        <SPAN style="color:#007F00">'   Select Black & White or Color Printing</SPAN>
        BAndW = _
            MsgBox("If you have a Color Printer, do you want to print this report in Black & White?" _
        & vbCrLf & "Pressing Yes will save lots of ink ;)", vbQuestion + vbYesNo, "Print Options")
    
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> BAndW
            <SPAN style="color:#00007F">Case</SPAN> vbYes
                .BlackAndWhite = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> vbNo
                .BlackAndWhite = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
        .Zoom = 55
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

javajoe

Board Regular
Joined
Nov 7, 2005
Messages
78
That's creative. I never thought about forcing it through Page Setup... However, using B&W through Page Setup gives slightly different results than if you force B&W through the Print dialog box (black backgrounds show up as white, white text shows us black, etc.).

Does anyone know of a way to do this as a parameter with ActiveWindow.SelectedSheets.PrintOut?
 

Forum statistics

Threads
1,136,354
Messages
5,675,296
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top