Solved

Excel Pie Chart - can I change the starting slice color in the automatic color index?

Posted on 2011-03-08
6
444 Views
Last Modified: 2012-05-11
I am using VBA to automate the creation of a bunch of worksheets with pie charts in Excel from MS Access. There will be two pie charts on each worksheet, and a worksheet for each Department (around 30 total).

I have figured out that the default color scheme is that first slice of each pie chart starts with ColorIndex #17, and proceeds from there one by one (18, 19, 20, etc)

What I hope to do is to have the Color Index start from a different number for the second chart, for example start with color index #7. I have searched online and have not seen any references to this at all. Is it possible?

The reason for this is that with the two pies side by side, people naturally think that the first color of each pie correspond to each other, but they do not.

I tried assigning colors to the specific labels for a slice (using Select Case), but this yielded some extremely ugly color combinations in some of the pies. Yuck.

Anyone have any idea if this can be done? Even more important, can someone tell me how to do it!?

Thanks,
Bonnie
0
Comment
Question by:BonnieH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35071627
Most things can be done with VBA. :)
My first question would in all honesty be:  do you really want pie charts at all? There is almost never a scenario where a different chart type isn't clearer than a pie chart.
0
 

Author Comment

by:BonnieH
ID: 35072546
Hello Rorya, I get your point about pie charts, but it's my boss's boss that has specified the pie charts, after conferring with his boss, and so Pie Charts it is!

Bonnie
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35072586
Rory, I can give you a scenario.... when you want to produce an eyewash ;)

Saqib
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 35081310
Ah, management. Understood. :)

Something like:
Sub testingfmtcht()
   FormatPieChart 7, ActiveSheet.ChartObjects(2).Chart
End Sub
Sub FormatPieChart(lngStartIndex As Long, cht As Chart)
' formats slices of a pie chart, starting at the provided colorindex
   Dim n As Long
   With cht.SeriesCollection(1)
      For n = 1 To .Points.Count
         With .Points(n).Interior
            .ColorIndex = lngStartIndex + n - 1
            .Pattern = xlSolid
         End With
      Next n
   End With
End Sub

Open in new window

0
 

Author Comment

by:BonnieH
ID: 35117385
Eureka! This worked. Thanks Rorya!
Sorry it took me a couple of days to respond. I did not get an email notification, and had assumed that this was beyond hope.

This is just what I wanted. Thanks so much.
Bonnie
0
 

Author Closing Comment

by:BonnieH
ID: 35117389
Perfect! Thanks so much!
Bonnie
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question