Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
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):
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):
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,
MSDN links:
ChartObject interface[^]
FillFormat interface[^]
P.S. An example Excel file is available here:[^]. 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
Edited 2-Jun-11 3:48am
losmac at 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?
2) pie chart doesn't exists and you create it?
R. Hoffmann at 25-May-11 14:58pm
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?
losmac at 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 at 2-Jun-11 9:55am
Hi losmac,
Thanks for your continuing interest! I've quickly uploaded a sample file to here: (20KB). I apologize for the stupid enforced delays before you can download, I didn't have time to pick a better free hosting service.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

In your example, you need to ClearFormats for ChartArea and then set ChartType and other options:
In Excel VBA:
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)
chrt.ChartType = xl3DPie
chrtA.Border.LineStyle = 0
With chrtG
    .VaryByCategories = True 'false = 1 color
    .FirstSliceAngle = 0
End With

    On Error Resume Next
    Set chrtG = Nothing
    Set chrtA = Nothing
    Set chrt = Nothing
    Set wsh = Nothing
    Exit Sub
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SetAutoSlices
End Sub
R. Hoffmann at 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!
losmac at 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).
losmac at 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 at 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!
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

This should work:
dataSeries.Interior.ColorIndex = XlColorIndex.xlColorIndexAutomatic
R. Hoffmann at 2-Jun-11 12:47pm
Thanks for your answer! I'll be able to test this tomorrow and provide feedback.
R. Hoffmann at 3-Jun-11 9:57am
Aaaaaand we have a WINNER! Your code does the trick. Thanks a million!!

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

  Print Answers RSS
0 OriginalGriff 393
1 Sergey Alexandrovich Kryukov 232
2 Richard Deeming 180
3 Snehasish_Nandy 171
4 ChauhanAjay 170
0 Sergey Alexandrovich Kryukov 6,367
1 OriginalGriff 5,588
2 CPallini 2,473
3 Richard MacCutchan 1,627
4 Abhinav S 1,530

Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 2 Jun 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100