Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Chart Series Name and Change Color

Status
Not open for further replies.
air1access

air1access

Technical User
Jan 27, 2008
123
US
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
 
Sort by date Sort by votes
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: 
[COLOR=#4E9A06]'On Error GoTo Err_btnColors_Click
'define chart colors for each series[/color]
  Dim myChtObj As Graph.Chart
  Dim x As Long [COLOR=#4E9A06]    'loop counter variable[/color]
  Dim xMax As Long [COLOR=#4E9A06] 'max loop for a slice[/color]
  Dim z As Long [COLOR=#4E9A06]    'loop counter variable for z loop[/color]
  Dim zMax As Long [COLOR=#4E9A06] 'max loop for the series[/color]

  Set myChtObj = Me.chrtAllegations.Object
  z = 1
  zMax = myChtObj.SeriesCollection.Count

  For z = 1 To zMax
    With myChtObj.SeriesCollection(z) [COLOR=#4E9A06]'pie chart has only one series collection - color each data point(slice) uniquely[/color]
      x = 1
      xMax = .Points.Count

      If xMax < 8 Then
        Set myChtObj = Nothing
       Else
        Do Until x = xMax
          Select Case x
            Case 1 [COLOR=#4E9A06]' "Bosswood"[/color]
            [COLOR=#4E9A06]'myChtObj.SeriesCollection(z).Points(x).Interior.Color = RGB(170, 0, 95) 'Plum[/color]

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
 
Upvote 0 Downvote
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....
 
Upvote 0 Downvote
First, always do the Preview before submitting your post (or replay)

If you type this:[pre][ignore]
Code: 
[/ignore]
If Something Or Other Then
    Do This
End If
[ignore]
[/ignore]
[/pre]
You get this: :)
Code: 
If Something Or Other Then
    Do This
End If


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

[pre]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[/pre]

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I was looking at that myself dhookom... I'm still trying to wrap my head around it...
 
Upvote 0 Downvote
Break after the chart objects of interest have been instantiated in your code.

FAQ707-4594.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Did you try...
Code: 
MsgBox SeriesCollection(i).Name

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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?

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

I’d suggest looking at [highlight #FCE94F]this[/highlight]...
Code: 
Select [highlight #FCE94F]SeriesCollection(i)[/highlight].Name
...in your Watch Window to discover if Name is available at this level.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Actually, the object needs full reference....
Code: 
‘
For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case [highlight #FCE94F]Me.chrtAllegations.[/highlight]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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
You already have a [tt]With Me.chrtAllegations.SeriesCollection(i)[/tt] statement, so it should be simply:

Code: 
For i = 1 To Me.chrtAllegations.SeriesCollection.Count
    With Me.chrtAllegations.SeriesCollection(i)
        Select Case [blue].Name[/blue]
            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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BallunarCrew
  • Locked
  • Question
Looking for a color chart 1
Replies
2
Views
134
BallunarCrew
BallunarCrew
TrekBiker
  • Locked
  • Question
Spurious Change of Fonts
Replies
3
Views
191
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Output To fails when name contains forward slash 1
Replies
7
Views
159
TrekBiker
TrekBiker
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
415
dhookom
dhookom
BlakeLacy1
  • Locked
  • Question
COPY ALL FOLDERS AND CONTENTS TO A NEW LOCATION
Replies
4
Views
148
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top