Fixed Slice Colors of a pie chart in Report

0
votes

I have an Access report with several Pie charts (placed in a GroupFooter). Access doesn't select unique colors for each slice category, which make the charts incomparable.

For example: in the first chart the slice for "Apple" is red, in the next Chart it's blue.

Each chart consists of five categories (let's say Apple, Orange, Lemon, Strawberry, Raspberry). If one category is zero, it's not shown in the chart. To make the charts comparable, I'd like to predefine the category colors.

I tried several codes that I found in the internet, but none of them works for me. Last one I tried was:

Private Sub Report_Current()
    ChartName.SeriesCollection(1).Points("Apple").Interior.Color = RGB(204, 51, 0)
    ChartName.SeriesCollection(1).Points("Orange").Interior.Color = RGB(255, 117, 117)
    ChartName.SeriesCollection(1).Points("Lemon").Interior.Color = RGB(197, 90, 17)
    ChartName.SeriesCollection(1).Points("Strawberry").Interior.Color = RGB(244, 177, 131)
    ChartName.SeriesCollection(1).Points("Raspberry").Interior.Color = RGB(255, 192, 0)
End Sub
2
vbams-accesscharts
Try using your code in the OnLoad function of the the form containing the graph and use numerical indexes instead of the name of the points. I.E: .Points(1). I am getting issues referencing the Points by name on my end. Perhaps it would resolve your issue as well. - hisoka
I have code in Format event for this. However, I use point index, not data value. - June7
Pie charts with more than three slices should be avoided. Use horizontal bar charts instead. Solves a lot of issues. One color, no legend, just information. For more info read perceptualedge.com/articles/visual_business_intelligence/… - teylyn
Hi everybody, thx for your comments. I tryed using numerical indexes as well, but it didn’t work for me neither. Besides, as 0-values are not represented in the charts, I think numercial indexes wouldn’t solve the problem, that same categories receive different colors across groups. @hisoka, unfortunately I don’t know the OnLoad function, could you describe in more detail how I should use it? Thx teylyn for your advice, but I need to display the distribution of categories relative to each other, thats why I need a pie chart. - Jeanette

2 Answers

0
votes

In continuation of what I mentioned in the comments...

For the sake of example, I created a blank form and added a pie graph from some random table.

enter image description here

In design view, on the property sheet, I added an OnLoad event to the form.

enter image description here

I added the following code and chose the same color for all three slices (Note that Graph1 is the name of my graph from the property sheet) :

Private Sub Form_Load()
    Graph1.SeriesCollection(1).Points(1).Interior.Color = RGB(255, 117, 117)
    Graph1.SeriesCollection(1).Points(2).Interior.Color = RGB(255, 117, 117)
    Graph1.SeriesCollection(1).Points(3).Interior.Color = RGB(255, 117, 117)
End Sub

When I open the form I see the following :

enter image description here

Hopefully, this might help resolve your issue.

0
votes

The graph/chart abilities changed significantly with Access 365. Code that works with the old graph component likely won't work in Access 365.

The new version uses a collection called ChartSeriesCollection, like this example:

With myChart.ChartSeriesCollection.Item(0)
 .BorderColor = RGB(0, 0, 0)
 .FillColor = RGB(210, 250, 210)
End With

Here is the Microsoft article that describes the collection: https://docs.microsoft.com/en-us/office/vba/api/access.chartseries.fillcolor