• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1949
  • Last Modified:

Fixing MS chart colors

This is similar ( or exactly) like the post "Title: User defined chart colors".

When I create a pie chart in access the colors of the same "items" change from one record to another.  Say I was charting fruit going to grocery stores.  The first record/store had 10 bannas and 20 apples going to it. The bannas come out say pink, and the apples blue on the pie chart.  The next record/store has 15 Bannas, 5 apples, & 666 oranges.  The bannas are no longer pink, the apples are no longer blue, and the oranges get assigned a color.  Is there a way to "fix" the colors so that each fruit is always the same color acorss all records?
1
deskchains
Asked:
deskchains
  • 13
  • 8
2 Solutions
 
puppydogbuddyCommented:
See this link:  http://blogs.msdn.com/alexgor/
Excerpt:
Chart types like Pie, assigns unique colors to each individual data point. You can still use Chart.Palette property or you can define different palettes for different series using Series.Palette property. Note that setting Series.Palette property will force all chart types including Column to use unique colors for each data point.

Important! To determine the color assigned to the series or data point from the palette you can call chart1.ApplyPaletteColors() method and then check Series or DataPoint.Color property.

0
 
maze-ukCommented:
Jumping on puppydogbuddy's entry:

maybe you can create a macro that does the following:
if series name = "banana" then series color = yellow, and so on...
0
 
deskchainsAuthor Commented:
puppydogbuddy
I feel like I went from the shallow end of the pool to the deep to quickly.  At best i am a code thief and plagiarizer.  

I create my pie chart in a form (so that I can edit it better) and then copy and past it into my report.  Before i start on a trial and error journey - are you suggesting that I follow the methods described in the link you provided and place it in an event(module or macro) of the chart properties?

Maze-UK - sorry I inadvertantly "no'd" out your solution - you might be on the right track i lack the knowledge to make that call.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
puppydogbuddyCommented:
Hi deskchains,
I didn't mean to send you over to the deep end.  LOL!!  All I was trying to convey is that as far as changing the colors  of the chart, the reference I cited says the colors can be changed using any of three methods: Pallette, DataSeries, or DataPoint,  To implement these methods manually right click on a datapoint or data series, etc and change the color as desired.

You can also change the colors using VBA.  Although Chart coding is not my area of  expertise, I took a stab at it (see below).  I have no idea if this will work, but I thought that it might be a good learning tool.
All that the code is intended to do is to change the color of all the Series at runtime using the dataseries method.

If you decide to play with the code, your need to replace the name of the chart control object that I used (MSChart1) with its actual name in your chart.
_________________________________________________________

Private Sub Form_Load()
'define chart colors for each series
Dim myChtObj As ChartObject
Dim x as Integer   'loop counter variable


Set myChtObj = MSChart1
x = 0
With myChtObj                   'color each data point the same for all series
      Do Until x = 3         'assuming there are 3 data series starting with Series 0
              SeriesCollection(x).Color = Color.Pastel
              x = x + 1
     Loop
End With
End Sub
0
 
puppydogbuddyCommented:
oops!,  typos
change>>>Dim myChtObj As ChartObject              to this>>>>>Dim myChtObj As Object

left out dot operator before the word "Series":
        SeriesCollection(x).Color = Color.Pastel            s/b      .SeriesCollection(x).Color = Color.Pastel
             
             
0
 
deskchainsAuthor Commented:
Thanks puppydogbuddy

The clicking of the series certainly does change the color....only for the first record though then its off to the races again.

I'll try the code, but, when do I tell it to run?  In the event of my report - or at the form level?  I'll experiment....
0
 
puppydogbuddyCommented:
run at form level .........event form load
If you want datapoints to be uniquely, but consistently colored all the Series, I  guess it would look like this, assuning each series in your chart had 7 datapoints:
x = 0
With myChtObj                   'color each data point uniquely but the same for all series
         Do Until x = 3         'assuming there are 7 datapoints in each of 3 data series starting with Series 0
                 .Series(x).Points(0).Color = Color.Red
                 .Series(x).Points(1).Color = Color.Blue
                 .Series(x).Points(2).Color = Color.Green
                 .Series(x).Points(3).Color = Color.Yellow
                 .Series(x).Points(4).Color = Color.Mayan
                 .Series(x).Points(5).Color = Color.Magenta                
                 .Series(x).Points(6).Color = Color.Teal
                  x = x + 1
        Loop
End With
0
 
puppydogbuddyCommented:
PS: if it does not run correctly in form load, try to run in form_open event
0
 
deskchainsAuthor Commented:
I feel I am approaching being a pain.

The number of series will vary from record to record,  Using my grocery store example - the first store get two kinds of fruit, the second ten, the third four, the fifth gets one....so on so forth.  There is a finite number of possible series per record so maybe the code will account for this?   My reading of your explanation leads me to think it wont....but im not much of one to ask.  Code a count of the series in the record prior to assigning color??
0
 
puppydogbuddyCommented:
If you want datapoints to be uniquely, but consistently colored all the Series, I  guess it would look like this, assuning each series in your chart had 7 datapoints:
x =  0

With myChtObj                   'color each data point uniquely but the same for all series
         Do Until x = .SeriesCollection.Count -1  'assumes 7 datapoints in each series starting with Series 0
                 .Series(x).Points(0).Color = Color.Red
                 .Series(x).Points(1).Color = Color.Blue
                 .Series(x).Points(2).Color = Color.Green
                 .Series(x).Points(3).Color = Color.Yellow
                 .Series(x).Points(4).Color = Color.Mayan
                 .Series(x).Points(5).Color = Color.Magenta                
                 .Series(x).Points(6).Color = Color.Teal
                  x = x + 1
        Loop
End With
0
 
puppydogbuddyCommented:
PS: you could put my code behind a button on your form instead of the form_load event.
0
 
deskchainsAuthor Commented:
Puppydogbuddy

Well im not sure what you mean by data points.  To better communicate I have stripped out all the extranious stuff in my database and included only the data required to produce the graph i am speaking of.  You will have to change the attached file extension back to .zip and then extract the database(hope im not breaking to many rules here and the file in fact goes through).  Open the report Landowner report2.  Watch how the colors change for the same "series" on different records.  In these examples i alway want the tree species to have the same color across all records.  

Forgive me if the code you already provided will do this.  I cant seem to wrap my mind around the logic.
example.txt
0
 
puppydogbuddyCommented:
deskchains,
I downloaded your file, but couldn't read it because it is an MS Access 2007 accdb and the latest version thaI I can work with is MS 2003 in 2000 db format. Irregardless, we should  be able to resolve your problem. Your question re datapoint ......... a datapoint in a pie chart is a slice of the pie, a series is a record comprising all slices related to the pie, and a series collection is all the records, each record ia a separate pie. How that translates to code depends on your data structure.  I think the last code I posted should work if based on the following structure: each datapoint having a fixed color in all the pies.
                     Series collection >>>>>Tree Species
                          Series>>>>>>>>>>> Palm Tree         Orange Tree      
                                   Data Point(0)>>>> Arica           Navel
                                   Data Point(1)>>>> Queen         Valencia

if your stucture is like this, the code would have to change.
                     Series collection >>>>>Species
                          Series>>>>>>>>>>>  Trees            Dogs      
                                   Data Point(0)>>>> Palm           Terrier
                                   Data Point(1)>>>> Ficus         Beagle

so try to convey your structure in the in the same heirarchital format I used above.
0
 
deskchainsAuthor Commented:
puppydogbuddy

ding ding ding ding....
Of course your versionis different because murphys law has never been kind to me.  

My structure is in fact like your secong example(thanks again murphy).  Each species has a count associated with it:

Series collection >>>>>Species
                          Series>>>>>>>>>>>  Trees           Value
                                   Data Point(0)>>>> Palm          10
                                   Data Point(1)>>>> Ficus         16
                                   Data Point(2) >>>> Oak           4

These species are found in groups of or  "stands" of trees and these represent one record - .  Each stand/record might have two species found in it, the next might have fifteen.  When I create the graph if the number of species in a stand is different it will assign a new color to the same species.  It seems to first key in on position of the species in the record set.  If record/stand 1 has ten species and the 6th one sugar maple, it gets colored green.  The second stand/record has seven species and the sixth record is still sugar maple the color will remain green.  If the third record has two species and the first or second one is sugar maple then the color changes from green to whatever the color the first/second position was assigned in the two previous records.  Sorting dosn't help since both the species and or value can/will change from record to record.  The attached is in now access 2000 formatt.  Launch the report and see what happens when going from the second to third record.  
example2.txt
0
 
puppydogbuddyCommented:
I could not open your report (missing bitmap), but I was able to open your form.  I don't believe you need code to fix your chart afterall....I think your main problem is that you have null datapoints in your table,  resulting that datapoint in being bypassed in the value count and therefore, throwing your tree species out of order, which in turn results in the color inconsistency.  You need to replace the null with a 0 so that the datapoint gets included in the value count as 0 which has no effect on the count value but enables the tree species to act as a placeholder.

The way to replace the nulls dynamically, is to create a query on your table and apply the nz (null to zero) function to every field that is supposed to contribute a value to the count.  Say for example the field containing the count value is fieldX .....go to the field row for fieldX and change the field to the following expression>>>>>>     fieldX: nz([fieldX[,0)      
0
 
puppydogbuddyCommented:
Have not heard from you, but thought I would let you that I am still working on this as time permits. Updating my previous comments:
1. You can eliminate the nulls in your data by setting the default value for the fields in your table to 0 or to a vey low value like .001.  You don't need to use the nz function I mentioned. previously.
2. There is very little documentation on VBA code syntax for manipulating charts using MS Graph, but I have made some progress....but am still working on it.
0
 
deskchainsAuthor Commented:
puppydogbuddy

well i had to turn in the project.....
As one of my trials to 'fix" the problem I made a table holding ONLY the data I wanted to chart. It listed every possible species = no gaps in the series.  If I leave the nulls I get a legend that leaves blanks where there is no value - think of a list of fifteen things.  If one or more is missing the list takes up as much space, but there are blanks where the "nulls" existed.  If I replace with zeros the the list stays full - including the zero values.  Both situations are ugly.

0
 
deskchainsAuthor Commented:
Though I would just go ahead
0
 
deskchainsAuthor Commented:
B grade because this would fix most peoples graphing issues, it was not a 100% fit to mine
0
 
puppydogbuddyCommented:
deskchains,
Sorry  haven't been able to get back you, but I have been really busy with clients this past week.  Nonetheless,  I finally found some documentation for using ms graph with vba. It is a separate download (see this link   http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=db7ff7ff-1e4b-42ca-bade-1738afb6fad9&displayLang=en  )

I am real close to creating vba code behind a button that does what you want.  I will post the code as soon as I get it working.  Thanks for the points.
0
 
puppydogbuddyCommented:
PS: this link is better than that last link I gave you:
                     http://msdn.microsoft.com/en-us/library/aa171197(office.11).aspx
0
 
puppydogbuddyCommented:
Here is the vba code behind a button.  It works if there are at least 7 data points for each series. Why 7?  that is how many were in the first series.

Private Sub btnColors_Click()
On Error GoTo Err_btnColors_Click

'define chart colors for each series
Dim myChtObj As Graph.Chart
Dim x As Long   'loop counter variable
Dim xMax As Long    'max loop for a slice
Dim z As Long    'loop counter variable for z loop
Dim zMax As Long    'max loop for the series
   

Set myChtObj = Me.Graph1.Object
    z = 1
    zMax = myChtObj.SeriesCollection.Count
   
   
For z = 1 To zMax
    With myChtObj.SeriesCollection(z)             'pie chart has only one series collection - color each data point(slice) uniquely
        x = 1
        xMax = .Points.Count
       
    If xMax < 7 Then
       Set myChtObj = Nothing
    Else
       Do Until x = xMax
            Select Case x
                Case 1     ' "Bosswood"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 3        'red
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbRed   'RGB(255,0,0)
                Case 2     '   "Beech"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 2        'white
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbWhite    'RGB(255,255,255)
                Case 3      ' "Bigtooth Aspen"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 4        'green
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbGreen   'RGB(0,255,0)
                Case 4   '  "Ironwood"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5        ' blue
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbBlue    'RGB(0,0,255)
                Case 5   '  "Quaking Aspen"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 8        ' cyan
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbCyan    'RGB(0,255,255)
                Case 6   '   "Sugar Maple"
                       myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 6        'yellow
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbYellow 'RGB(255,255,0)
                Case 7   '   "White Ash"
                       myChtObj.SeriesCollection(z).Points(x).Interior.Color = 7             'magenta
                       myChtObj.Legend.LegendEntries(x).LegendKey.Interior.Color = vbMagenta  'RGB(255,0,255)
                Case Else
                       Exit Sub
            End Select
       x = x + 1
       Loop
    End If
    z = z + 1
    End With
Next

Exit_btnColors_Click:
    Set myChtObj = Nothing
    Exit Sub

Err_btnColors_Click:
    MsgBox Err.Description
    Resume Exit_btnColors_Click
   
End Sub
 
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 13
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now