Click here to Skip to main content
Rate this: bad
good
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,
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
Edited 2-Jun-11 3:48am
v2
Comments
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? or 2) pie chart doesn't exists and you create it?
R. Hoffmann at 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?
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: 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This should work:
 
dataSeries.Interior.ColorIndex = XlColorIndex.xlColorIndexAutomatic
  Permalink  
Comments
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!!
Rate this: bad
good
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)
 
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
  Permalink  
v2
Comments
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! Ralf

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 Tadit Dash 350
2 Manas Bhardwaj 319
3 OriginalGriff 243
4 Sergey Alexandrovich Kryukov 240
0 Sergey Alexandrovich Kryukov 9,530
1 OriginalGriff 5,716
2 Peter Leow 4,320
3 Maciej Los 3,540
4 Abhinav S 3,373


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