change a color in a line chart

[rutica]

I am using Access 2003. I have a question about charts please:

In a report (not a form), I have a line chart with $ in the vertical axis, Date in the horizontal axis, and 3 values plotted as the data: PlannedCost, ActualCost and TotalCost.

Access made my TotalCost line light yellow and I want to change the color. It seems so simple, but I can't do it.

I can't do it because when looking at my chart in Design view, I only see one line. (I should see 3 lines: PlannedCost, ActualCost and TotalCost, but I don't).

If I could see my 3 lines in Design view, I could right click on the TotalCost line and go to Format Data Series and change the color. My problem is that I can't right click on TotalCost line because it's not displayed.

The weird thing is, when I first created the chart, I had only one value that I called Costs. Later I renamed the value and added 2 more values.

But when I look at the chart in Design view, I see Costs. In Print Preview, it looks fine, but in Design view, it shows me my old chart. It's like the chart hasn't refreshed.

I went in the Code window and clicked Tools, References and checked Microsoft Graph 11.0 Object Library. But that didn't help.

I included two screenshots.

Any ideas?

Thanks,

 

[ChrisO]

You should be able to force the colours you want at runtime: -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    With chtYourChartNameGoesHere
        .SeriesCollection(1).Border.Color = vbRed
        .SeriesCollection(2).Border.Color = vbGreen
        .SeriesCollection(3).Border.Color = vbBlue
    End With

End Sub

 

[rutica]

thanks for writing. I'm getting 'object doesn't support this property or method' and it highlights this line:
.SeriesCollection(1).Border.Color = vbRed

here is my code. my graph is named Graph3:

Private Sub Report_Open(Cancel As Integer)
 
    With Graph3
         .SeriesCollection(1).Border.Color = vbRed
        .SeriesCollection(2).Border.Color = vbGreen
        .SeriesCollection(3).Border.Color = vbBlue
    End With
End Sub

 

[ChrisO]

Try the code in the Detail Format event: -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

 

[rutica]

Sorry, I see that Detail Format event is what you originally had in your first email. I changed it and now it works!! wow. i have spent over 1.5 hours on this! thanks!!!!!

Is there a way to change the color of the markers as well?

I went to excel and ran a macro in order to get the code, but it's not working in access.

this is what i tried:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Graph3
        .SeriesCollection(1).Border.Color = vbRed
        .SeriesCollection(1).MarkerBackgroundColorIndex = vbRed
        .SeriesCollection(1).MarkerForegroundColorIndex = vbRed
 
        .SeriesCollection(2).Border.Color = vbGreen
        .SeriesCollection(2).MarkerBackgroundColorIndex = vbGreen
        .SeriesCollection(2).MarkerForegroundColorIndex = vbGreen
 
        .SeriesCollection(3).Border.Color = vbBlue
        .SeriesCollection(3).MarkerBackgroundColorIndex = vbBlue
        .SeriesCollection(3).MarkerForegroundColorIndex = vbBlue
    End With
End Sub

Do you know why the graph lines aren't showing up in Design view? That would be the easiest: simply right click on the line.

 

[ChrisO]

Try: -

.SeriesCollection(1).Border.Color =vbRed
.SeriesCollection(1).MarkerBackgroundColor = vbRed
.SeriesCollection(1).MarkerForegroundColor = vbRed

 

[ChrisO]

You may be interested in the demo here: -

http://www.access-programmers.co.uk/forums/showthread.php?t=189196

 

[rutica]

it works! thanks!! you're the best.

I'll take a look at the demo later. i'm at work and can't download. thanks for sharing the demo.

Do you know why the graph lines aren't showing up in Design view? That would be the easiest: simply right click on the line.

i have another chart question, but i'll start a new thread since it's a new topic. Hope to see you on my next thread.

 

[ChrisO]

As for why it’s not showing up in the report in design view, I don’t know for sure.

You could try copying the chart into a new blank Form, run it and then copy it back to a new Report.
When the chart is formatted in the Form it should hold its formatting when copied back to the Report.

 

[rutica]

thanks. i tried what you said:
"copying the chart into a new blank Form, run it and then copy it back to a new Report. When the chart is formatted in the Form it should hold its formatting when copied back to the Report."

but when i copied the chart from the report to the form, the form also showed just Costs (instead of PlannedCost, ActualCost and TotalCost). So running it and copying it back to a new report didn't change anything.


One weird thing is that using your code caused the data labels to be shown on two of the lines, even though i had turned that off.

so i added some code to force the data labels to not be shown:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Graph3
        .SeriesCollection(1).Border.Color = vbRed
        .SeriesCollection(1).MarkerBackgroundColor = vbRed
        .SeriesCollection(1).MarkerForegroundColor = vbRed
        .SeriesCollection(1).ApplyDataLabels AutoText = False
     
        .SeriesCollection(2).Border.Color = vbGreen
        .SeriesCollection(2).MarkerBackgroundColor = vbGreen
        .SeriesCollection(2).MarkerForegroundColor = vbGreen
        .SeriesCollection(2).ApplyDataLabels AutoText = False
   
        .SeriesCollection(3).Border.Color = vbBlue
        .SeriesCollection(3).MarkerBackgroundColor = vbBlue
        .SeriesCollection(3).MarkerForegroundColor = vbBlue
        .SeriesCollection(3).ApplyDataLabels AutoText = False
    End With
End Sub

 

[ChrisO]

Try uploading the table and report to site and I’ll have a look at it.

 

[rutica]

this is really weird. i started modifying my database to post it here, but now i see my chart looks correct in Design view! so somehow the problem is fixed. your code is great, but now i can simply right click on the line and select my own color.

but...

I commented out your code and my colors stayed red, blue and green! That's weird too.

anyway, your code was fantastic and worked perfectly. thank you so much for your help!

 

[ChrisO]

Yes, when the code runs to set a colour in a Series it switches the Series from Automatic to Custom and then sets the colour.
It appears that the chart is now remembering its format but they don’t always do that.

Good luck with your charting.