Change Excel pie chart slice colours to automatic

Question

5.00/5 (1 vote)

See more:

Hi guys,

Apologies in advance for the long post!

I have to change the colours of the slices in a pie chart to automatic, using COM automation. In other words, I need to programmatically achieve the same as if I had right-clicked a pie chart in Excel (2007), selected "Format Data Series...", selected the Fill category on the left, set it to "Automatic", and have "Vary Colours By Slice" ticked.

When the fill is set to automatic, then each slice gets a unique colour (this is the default setting in Excel). I'm in a situation where I have pie charts (both 3D and 2D) with their Fill set to "Solid Fill", meaning all slices have the same colour, and I need to get this back to automatic, so that each slice has its own colour.

I can obtain the chart object, and set its "Vary Colours By Slice" property to true (VB):

VB

Dim group As Excel.ChartGroup = CType(chartObject.Chart.PieGroups(0), Excel.ChartGroup) ' chartObject refers to the pie chart I've already obtained (Microsoft.Office.Interop.Excel.ChartObject)
group.VaryByCategories = True

However, I cannot figure out how to set the Fill itself to "Automatic". I guess a solution would be to assign different colours to each slice myself, but I'd really prefer to have Excel do this automatically.

The format of the data series can be accessed by using (following on from above code):

VB

Dim dataSeries As Excel.Series = CType(group.SeriesCollection(1), Excel.Series)
dataSeries.Format.Fill ' Of type Microsoft.Office.Interop.Excel.FillFormat

Using the Type property (of type Microsoft.Office.Core.MsoFillType) allows me to distinguish between a solid (all slices the same colour) and automatic (each slice a different colour) fill:
msoFillMixed = automatic assignment
msoFillSolid = all slices the same colour

Unfortunately the Type property is read-only, and doesn't allow me to change it that way. Instead, one is supposed to use methods, also part of Microsoft.Office.Interop.Excel.FillFormat, to set the fill type (e.g. Solid(), Patterned() etc). It's just that there's no method to set it to automatic.

So, do any of the few people who survived my long-winded explanation have any idea on how to achieve this?

Many thanks,
Ralf

MSDN links:
ChartObject interface[^]
FillFormat interface[^]

P.S. An example Excel file is available here: http://www.easy-share.com/1915820675/Demo.xls[^]. My apologies for the two enforced waits on the download link; I didn't have time to find a better free hosting service. I hate those delays too.

Posted 20-Apr-11 4:06am

R. Hoffmann

Updated 2-Jun-11 3:48am

v2

Add a Solution

Comments

Maciej Los 25-May-11 14:19pm

Can you place a little more code? I'm not sure what you mean writing this words: "I'm in a situation where I have pie charts (both 3D and 2D) with their Fill set to "Solid Fill", meaning all slices have the same colour, and I need to get this back to automatic, so that each slice has its own colour."
Did you mean:
1) pie chart exists and you change it data source?
or
2) pie chart doesn't exists and you create it?

R. Hoffmann 25-May-11 14:58pm

Hi,

The pie chart already exists, and I only need to change its formatting (the underlying data stays the same). Currently the pie charts have multiple slices, but all slices have the same colour (normally, when you create a new pie chart in Excel, then the colours are set to "Automatic", and Excel makes each slice a different colour). Basically I'm just looking for a way to get Excel to once again automatically make the slices in the pie charts each have a different colour.

Does that make more sense?

Maciej Los 31-May-11 17:48pm

Yes, it make sense. Can you place an example xls file with both charts on any free file server? I need to look at these charts.

R. Hoffmann 2-Jun-11 9:55am

Hi losmac,

Thanks for your continuing interest! I've quickly uploaded a sample file to here: http://www.easy-share.com/1915820675/Demo.xls (20KB). I apologize for the stupid enforced delays before you can download, I didn't have time to pick a better free hosting service.

2 solutions

Solution 1

In your example, you need to ClearFormats for ChartArea and then set ChartType and other options:

In Excel VBA:

VB

Option Explicit

Sub SetAutoSlices()
Dim chrt As Chart, chrtA As ChartArea, chrtG As ChartGroup
Dim wsh As Worksheet

On Error GoTo Err_SetAutoSlices

Set wsh = ThisWorkbook.Worksheets(1)
Set chrt = wsh.ChartObjects(1).Chart
Set chrtA = chrt.ChartArea
Set chrtG = chrt.ChartGroups(1)

chrtA.ClearFormats
chrt.ChartType = xl3DPie
chrtA.Border.LineStyle = 0
With chrtG
    .VaryByCategories = True 'false = 1 color
    .FirstSliceAngle = 0
End With


Exit_SetAutoSlices:
    On Error Resume Next
    Set chrtG = Nothing
    Set chrtA = Nothing
    Set chrt = Nothing
    Set wsh = Nothing
    Exit Sub

Err_SetAutoSlices:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SetAutoSlices
End Sub

Posted 2-Jun-11 6:16am

Maciej Los

Updated 2-Jun-11 6:28am

v2

Comments

R. Hoffmann 2-Jun-11 12:37pm

Thank you very much! Unfortunately this doesn't seem to work under Office 2010 (which is the only version I have available at the moment for testing). The ClearFormats call causes a border to appear around the chart, which the LineStyle = 0 statement later clears, and the position of the slices change when FirstSliceAngle is set, but those are the only changes. The solid colour unfortunately remains..

Tomorrow I'll test this under Office 2007 to see if that makes a difference.

Thanks again!

Maciej Los 2-Jun-11 17:34pm

I don't have Office 2010. I have MS Office 2003. So, i can't help you in MS Office 2010. Look at msdn site to see the differences between MS Office version (objects, method).

Maciej Los 2-Jun-11 17:36pm

I don't have MS Office 2k10, i have MS Office 2k3. So... look at msdn site to see the differences between version (objects, methods).

R. Hoffmann 3-Jun-11 10:05am

I've just tested this again in Office 2007, and it also doesn't change the colours. Microsoft may have changed some or other implementation somewhere between Office 2003 & 2007, that breaks this solution. In any case, I'm very grateful for all your assistance! Member Ruard has provided an alternative idea that did the trick, so this question can finally be closed :)

Thanks again!
Ralf

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ruard · Accepted Answer · 2011-06-02T06:23:00

Solution 2

This should work:

dataSeries.Interior.ColorIndex = XlColorIndex.xlColorIndexAutomatic

Posted 2-Jun-11 6:23am

Ruard

Comments

R. Hoffmann 2-Jun-11 12:47pm

Thanks for your answer! I'll be able to test this tomorrow and provide feedback.

R. Hoffmann 3-Jun-11 9:57am

Aaaaaand we have a WINNER! Your code does the trick. Thanks a million!!