INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Chart Series Name and Change Color

Chart Series Name and Change Color

(OP)
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

RE: Chart Series Name and Change Color

(OP)
Any body have any ideas for a solution?

RE: Chart Series Name and Change Color

I have a couple suggestions. First, if you want quick assistance consider using TGML to format your code to make it easier to read:

CODE --> vba

'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 

Second, the chart object properties are difficult to determine. I will typically record a macro in Excel that steps through what I want to do with a chart in Access. I then copy and paste the code into my Access form or report and make a few modifications.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Chart Series Name and Change Color

(OP)
Ok - I'll need to learn how to use TGML. In my post options, I have TGML flagged, but evidently there is more to it...

I don't really understand what you mean with " record a macro in Excel that steps through what I want to do with a chart in Access".
I'm not sure how to create an excel macro that keeps the colors the same for a series....

RE: Chart Series Name and Change Color

First, always do the Preview before submitting your post (or replay)

If you type this:
[code]
If Something Or Other Then
    Do This
End If
[/code]
 
You get this: smile

CODE

If Something Or Other Then
    Do This
End If 


---- Andy

There is a great need for a sarcasm font.

RE: Chart Series Name and Change Color

(OP)
Below is what I am working with...
I'm trying to keep a series the same color each time the chart is updated... Based on drop down box selections.
I was wondering if there is a way to loop through the series, and for each name in the series change the color of the bar in the chart depending on the series name...


CODE

'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 

RE: Chart Series Name and Change Color

Do you indent your code?

My second suggestion was to create a similar chart in Excel. Then record a macro that changes the properties you want to change in Access. You can stop the recording and find the code in Excel which can be used to update your code in Access.

I really don't know what the VBA code and properties are for charts. Recording in Excel is my typical method for finding the code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Chart Series Name and Change Color

I am not really sure air1access is talking about the keeping "a series the same color each time the chart is updated" in Excel. There is no mentioning of Excel in the code provided.
So are the charts on your Access form, or are they in Excel? Or do they start in Excel and are transferred to Access? Or have nothing to do with Excel?

It would be nice to have code indented, and green text for commented out code...


---- Andy

There is a great need for a sarcasm font.

RE: Chart Series Name and Change Color

Andy,
I only use Excel to write my charting code since it has the macro recorder. Once the code has been recorded and copied to Access, you can delete the Excel file (or keep it for reference).

I have found the chart objects in Excel and Access to share many of the same properties and methods.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Chart Series Name and Change Color

(OP)
I am working with a column chart type in Access. The chart is on a form.
The user selects from different combo boxes to choose what they want in the chart.

The code I provide, changes the color of the column for each series up to the number of series specified.
I need for it to loop through the chart objects and change the color of the column by the series name. I hope I'm using the correct terms, not familiar with chart objects.
So that way the channel "CallCenter/eVendor" is always the same color, no matter what year or quarter is selected from the drop downs.

Hope this helps..!!
Below is the dataset for the chart.

Year/Qtr	CallCenter/eVendor	FMO	National Agencies
2016 Qtr 1	148	239	87
2016 Qtr 2	228	193	70
2016 Qtr 3	196	129	49
2016 Qtr 4	144	99	27
2017 Qtr 1	305	171	83
2017 Qtr 2	286	124	63
2017 Qtr 3	261	80	32
2017 Qtr 4	122	38	10 

RE: Chart Series Name and Change Color

You might want to check this thread which I found using google.

Have you added a breakpoint in your code and then opened the debug window to play with the properties?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Chart Series Name and Change Color

(OP)
I was looking at that myself dhookom... I'm still trying to wrap my head around it...

RE: Chart Series Name and Change Color

(OP)
Ok... I kinda got some where.
The code below works for a column chart and sets the color of the column in each series separately...
I'm needing it to be where it sets the column color based on the Series name...
It always errors when I add something like: Series.Name = "dfassdffasd"

Any suggestions?

CODE

For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case i
            Case "1": .Interior.Color = RGB(33, 189, 226)
            Case "2": .Interior.Color = RGB(123, 210, 36)
            Case "3": .Interior.Color = RGB(0, 95, 117)
            Case "4": .Interior.Color = RGB(180, 215, 107)
            Case Else: .Interior.Color = RGB(92, 154, 27)
        End Select
    End With
Next I 

RE: Chart Series Name and Change Color

Did you try...

CODE

MsgBox SeriesCollection(i).Name 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

CODE

‘
For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case SeriesCollection(i).Name
            Case "SeriesName1": .Interior.Color = RGB(33, 189, 226)
            Case "SeriesName2": .Interior.Color = RGB(123, 210, 36)
            Case "SeriesName3": .Interior.Color = RGB(0, 95, 117)
            Case "SeriesName4": .Interior.Color = RGB(180, 215, 107)
            Case Else: .Interior.Color = RGB(92, 154, 27)
        End Select
    End With
Next I 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

(OP)
Thanks for the help SkipVought..!

I tried your example and I keep getting an error on:
Select Case SeriesCollection(I).name

The error message is:
Compile Error: Sub or Function not defined

Any ideas/suggestion?

RE: Chart Series Name and Change Color

On 6 Dec 17 23:31 I posted a link to an FAQ.

I’d suggest looking at this...

CODE

Select SeriesCollection(i).Name 
...in your Watch Window to discover if Name is available at this level.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

Actually, the object needs full reference....

CODE

‘
For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case Me.chrtAllegations.SeriesCollection(i).Name
            Case "SeriesName1": .Interior.Color = RGB(33, 189, 226)
            Case "SeriesName2": .Interior.Color = RGB(123, 210, 36)
            Case "SeriesName3": .Interior.Color = RGB(0, 95, 117)
            Case "SeriesName4": .Interior.Color = RGB(180, 215, 107)
            Case Else: .Interior.Color = RGB(92, 154, 27)
        End Select
    End With
Next I 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

You already have a With Me.chrtAllegations.SeriesCollection(i) statement, so it should be simply:

CODE

For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case .Name
            Case "SeriesName1": .Interior.Color = RGB(33, 189, 226)
            Case "SeriesName2": .Interior.Color = RGB(123, 210, 36)
            Case "SeriesName3": .Interior.Color = RGB(0, 95, 117)
            Case "SeriesName4": .Interior.Color = RGB(180, 215, 107)
            Case Else: .Interior.Color = RGB(92, 154, 27)
        End Select
    End With
Next I 


---- Andy

There is a great need for a sarcasm font.

RE: Chart Series Name and Change Color

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

RE: Chart Series Name and Change Color

Duh! Thanks, Andy!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

(OP)
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...?

RE: Chart Series Name and Change Color

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

(OP)
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...

RE: Chart Series Name and Change Color

You can’t do a watch on .Name

Do a watch on Me.chrtAllegations.SeriesCollection(i) 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 i to observe the value of i when you Debug.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Chart Series Name and Change Color

(OP)
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...

RE: Chart Series Name and Change Color

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close