Solved print excel chart in color using vba

April 9, 2014 at 12:12:58
Specs: Windows 7
I am trying to use a macro to print a chart in color but I am not having any luck. Does anyone have any ideas? I am using Excel 2010

See More: print excel chart in color using vba

Report •


#1
April 9, 2014 at 12:26:53
What are you not have "any luck" with?

Is it not printing? Is it not printing in color? Is it not printing to the correct printer? Something else?

Since we can't see your system from where we are sitting, we need some more details as to what you are trying to to do and what is or isn't happening.

Of course, having a copy of the macro you are using might help also.

If you are going to post your code, please click on the following line and read the instructions found via that link before posting your macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 9, 2014 at 12:35:49
Sorry for not explaining properly. I have created a macro to print the chart but I can't seem to get it to print in color (it doesn't capture my Printer Properties change)

Sub CycleTime2()
    Range("B3").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Sheets("Plant1_CycleTimeControlChart").Select
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWorkbook.Close
End Sub

I have searched and found the .sendkeys function but I can't seem to get that to work either?

The following is the code for that option

Sub CycleTime5()
    Range("B3").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveSheet.ChartObjects("Chart 5").Activate
    
    Application.SendKeys "%fp%r", True

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}", True

    Application.SendKeys "c", True

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}", True

    Application.SendKeys "{RETURN}"
    
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWorkbook.Close
End Sub

Thanks
Sandi


Report •

#3
April 9, 2014 at 15:38:35
✔ Best Answer
If my printer properties are set to Color, then this code will print a sheet in B&W:

Sub PrintBW
  ActiveSheet.PageSetup.BlackAndWhite = True
  ActiveSheet.PrintOut
End Sub

and this code will print the sheet in color:

Sub PrintColor
  ActiveSheet.PageSetup.BlackAndWhite = False
  ActiveSheet.PrintOut
End Sub

However, if my printer properties are set to Black/Grayscale, the False setting doesn't appear to do anything. i.e. this code will not print the sheet in color.

Sub PrintColor
  ActiveSheet.PageSetup.BlackAndWhite = False
  ActiveSheet.PrintOut
End Sub

Seems like the printer properties must be set to color for the "False" argument to work.

Another option is to set up 2 printers on your system and print directly to the B&W printer or the Color printer in your code. I realize that in some environments, that may not be possible.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 10, 2014 at 05:05:25
Thank you for your reply, unfortunately it is set to B&W so I will check with IT about setting about one in Color.

Late yesterday I also found this work around that pulls up the printer list and you can change the settings from there. I was really hoping they wouldn't have to choose anything but it will work as well.

Application.Dialogs(xlDialogPrinterSetup).Show

Thanks again!
Sandi


Report •

#5
April 10, 2014 at 05:51:13
I hope your IT department can help you out. I'd also like to explain my second suggestion in a little more detail:

Another option is to set up 2 printers on your system and print directly to the B&W printer or the Color printer in your code.

When my kids were living at home I "cloned" my ink jet printer so that it appeared as 2 different printers on our systems. One printer had the default properties set to B&W, the other was set to Color. I set the default printer on all of our systems to be the printer with the B&W properties. That forced us to manually choose the color printer when we needed it.

That saved a lot of expensive color ink.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 10, 2014 at 06:01:30
That does make more sense and thank you for the explanation, you have been most helpful!

Sandi


Report •


Ask Question