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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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....
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....
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
.Series(x).Points(1).Color
.Series(x).Points(2).Color
.Series(x).Points(3).Color
.Series(x).Points(4).Color
.Series(x).Points(5).Color
.Series(x).Points(6).Color
x = x + 1
Loop
End With
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
.Series(x).Points(1).Color
.Series(x).Points(2).Color
.Series(x).Points(3).Color
.Series(x).Points(4).Color
.Series(x).Points(5).Color
.Series(x).Points(6).Color
x = x + 1
Loop
End With
PS: if it does not run correctly in form load, try to run in form_open event
ASKER
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??
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??
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
.Series(x).Points(1).Color
.Series(x).Points(2).Color
.Series(x).Points(3).Color
.Series(x).Points(4).Color
.Series(x).Points(5).Color
.Series(x).Points(6).Color
x = x + 1
Loop
End With
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
.Series(x).Points(1).Color
.Series(x).Points(2).Color
.Series(x).Points(3).Color
.Series(x).Points(4).Color
.Series(x).Points(5).Color
.Series(x).Points(6).Color
x = x + 1
Loop
End With
PS: you could put my code behind a button on your form instead of the form_load event.
ASKER
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
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
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.
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.
ASKER
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
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
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)
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)
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.
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.
ASKER
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.
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.
ASKER
Though I would just go ahead
ASKER
B grade because this would fix most peoples graphing issues, it was not a 100% fit to mine
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.
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.
PS: this link is better than that last link I gave you:
http://msdn.microsoft.com/en-us/library/aa171197(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa171197(office.11).aspx
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.
For z = 1 To zMax
With myChtObj.SeriesCollection(
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(
myChtObj.Legend.LegendEntr
Case 2 ' "Beech"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 3 ' "Bigtooth Aspen"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 4 ' "Ironwood"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 5 ' "Quaking Aspen"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 6 ' "Sugar Maple"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 7 ' "White Ash"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
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
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.
For z = 1 To zMax
With myChtObj.SeriesCollection(
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(
myChtObj.Legend.LegendEntr
Case 2 ' "Beech"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 3 ' "Bigtooth Aspen"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 4 ' "Ironwood"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 5 ' "Quaking Aspen"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 6 ' "Sugar Maple"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
Case 7 ' "White Ash"
myChtObj.SeriesCollection(
myChtObj.Legend.LegendEntr
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
maybe you can create a macro that does the following:
if series name = "banana" then series color = yellow, and so on...