Chart Series Name and Change Color

[air1access]

I'm trying to put together some code for looping through seriescollection names of a chart, and change the color of the bar depending on the series name...

Any ideas or suggestions?

Below is what I am working with:

'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.chrtAllegations.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 < 8 Then
Set myChtObj = Nothing
Else
Do Until x = xMax
Select Case x
Case 1 ' "Bosswood"
'myChtObj.SeriesCollection(z).Points(x).Interior.Color = RGB(170, 0, 95) 'Plum

'Select Case myChtObj.SeriesCollection(z).Points(x).Name
' Case "FMO"
'myChtObj.SeriesCollection(z).Points(x).Interior.Color.RGB = RGB(0, 176, 80) ' Green
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 'Blue
'MsgBox myChtObj.SeriesCollection(1).Points(1).Name
Dim varValues As Variant
Dim strName As String
varValues = myChtObj.SeriesCollection(z).Points(x).XValues
strName = varValues(z)
MsgBox strName
'End Select

Case 2 ' "Beech"
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 'Blue

Case 3 ' "Bigtooth Aspen"
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 'green

Case 4 ' "Ironwood"
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 ' blue

Case 5 ' "Quaking Aspen"
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 ' cyan

Case 6 ' "Sugar Maple"
myChtObj.SeriesCollection(z).Points(x).Interior.ColorIndex = 5 'yellow

Case 7 ' "White Ash"
myChtObj.SeriesCollection(z).Points(x).Interior.Color = 5 'magenta

Case 8 ' "White Ash"
myChtObj.SeriesCollection(z).Points(x).Interior.Color = 4 'magenta

Case Else
Exit Sub
End Select
x = x + 1
Loop
End If
z = z + 1
End With
Next

 

[air1access]

I'm starting to believe that there is no .Name property with the Chart object in MS Access.
I still get the same error...

 

[SkipVought]

Duh! Thanks, Andy!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[air1access]

Sooooo......?
What...

That's it..?
I'm learning - what can I say?

Is there another method to do what I'm trying to do in Access...?

 

[SkipVought]

The code DOES work to change the Series Interior Color. I ran similar code to check.

Series DOES have a Name property.

Use the Watch Window to check the INDEX value at the point of the error.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[air1access]

Ok - I have been working to understanding the Watch Window. Mostly greek to me, but I think I'm catching on..

I put a watch on "Select Case .Name"...
The Watch Window shows the value for Name -- "Microsoft Access"

Not sure what this means or what to do next...

 

[SkipVought]

You can’t do a watch on [highlight #FCAF3E].Name[/highlight]

Do a watch on [highlight #FCE94F]Me.chrtAllegations.SeriesCollection(i)[/highlight] and when you get an error, hit Debug and when the Watch Window opens, hit the + next to this object.

And do a watch on [highlight #FCE94F]i[/highlight] to observe the value of i when you Debug.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[air1access]

the value of i when viewed in the Watch Window is the series number...
It does not show "CallCenter/eVendor", "External Partners", etc - such as the fields used for the chart.
Am I doing it wrong? I'm trying..! lol

I guess I can rename my field values to match the series index..?
I don't know what else to do...

 

[SkipVought]

Perhaps you defined the ranges of your chart so as not to include the range that defines the names of your series.

Can you upload your workbook?

Short of that if you were to right-click in your chart and select Select Data, the Select Data Source window will pop up. You should see your your series. Select one and hit the Edit button. Now the Edit Series window pops up, showing Series Name. Is that what you expect?

BTW, depending on the way you orient your source data selection, the Series Name the YOU expect to see might just disappear!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]