Solved

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

Posted on 2011-03-08
6
434 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
  • 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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