How to select a specific printer and select color printing

Question

I have an Excel VBA macro that tries to print in color but it only prints in black and white.

I have tried two methods but failed.

Method 1: ActiveSheet.PageSetup.BlackAndWhite = False. This code doesn't seem to work in my environment. I am also not allowed to add another printer queue that is in color.

Method 2: Application.Dialogs(xlDialogPrinterSetup).Show. I can bring up a list of printers in my environment using this code but don't know how to select the correct printer and select color.

ActiveSheet.PageSetup.BlackAndWhite = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="\\printer\printer1", Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("REPORT").Select

Would like to print in color either by expanding on either of the 2 methods I've provided.

majka majka · Accepted Answer · 2019-02-01T13:13:20

I suspect it is a problem of the printer settings, not of your code. Check how the printer is actually set. If you are on Win 10, you have an additional problem - you need to disable the automatic managing of print jobs from systems, it keeps reverting to the last setting for a given app and ignores your manual settings.

What ActiveSheet.PageSetup.BlackAndWhite = False does is an Excel setting (you are sending it to the printer in color). IMHO, the problem is in the actual printer setting - it is set to B&W there, meaning the conversion happens in the printer. You cannot change this from VBA, at least not easily.

What you can do (but probably need administrator access), is to set a second (third) copy of the printer in the system, set it to color printing there, and before printing choose this "other" printer.

I am using this method for Word - set one "printer" for Envelopes, one "printer" for double sided printing and the original printer has the normal setting set by the company. Every one of it is physically the same printer.

The solution is then instead of ActivePrinter:="\\printer\printer1" to change the ActivePrinter to the one printing in color, and after the printout back again.

Edit:

Reading again - is the "\\printer\printer1" the correct one? You can look in the system how the printers are connected or simply record a macro when you send something to a color printer and look there. It might be, that you simply need to set Application.activeprinter= to another one, for example Application.activeprinter="\\printer\printer2".

How to select a specific printer and select color printing

1 Answers