9 Replies - 2336 Views - Last Post: 04 April 2013 - 05:32 AM Rate Topic: -----

#1 Domien  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 29-September 10

Excel color graph points of multiple series collections

Posted 29 March 2013 - 05:35 AM

Hi,

I'm creating a small excel sheet for our Maintenance department.

They enter the score of their normal maintenance and preventive maintenance each month for each production line.

Line 1 1/03/2013 PREVENTIVE 100
Line 1 1/03/2013 NORMAL 98

They do this in a sheet just for this.
From this sheet I create a pivot table which displays the needed values based on their selection in several slicers.
Based on the value (100, 98, 88) I then color these cell values.

Form this pivot table I create a graph which displays the exact same data. But I would like the graph to display the same colors. So I go over each series collection, and check the values. I then modify those points formatting based on the value they have.
This works perfectly if I have one series collection. When I have multiple changing the color of a point also changes the color of a point in another series collection.

Sub PivotChartChangeColors(chartName As String)

On Error GoTo ErrHandler

Dim values As Variant
Dim s As Long
Dim s2 As Long
Dim p As Long

For s = 1 To Charts(chartName).SeriesCollection.Count
    values = Charts(chartName).SeriesCollection(s).values

    For p = 1 To UBound(values)
        With Charts(chartName).SeriesCollection(s)
        Dim theValue As Variant
        theValue = values(p)
                                                                                                                              
            If theValue = 100 Then
                .Points(p).Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
            ElseIf theValue >= 90 Then
                .Points(p).Format.Fill.ForeColor.RGB = RGB(226, 107, 10)
            ElseIf theValue < 90 And theValue > 1 Then
                .Points(p).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            ElseIf theValue = 1 Then
                .Points(p).Format.Fill.ForeColor.RGB = RGB(217, 217, 217)
            ElseIf theValue = 0 Or theValue = Null Then
                .Points(p).Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
            End If
            
            'Do some other formatting like adding labels and such
             
            
     End With
    Next
Next

'add chart title and such...
Exit Sub

ErrHandler:
MsgBox ("there was a problem updating the graph, try again or contact Technical IT")
End Sub




Even when I manually change the color of 1 point (or by the code below), it changes the color of a point in another series collection.
Does this make sense to anyone? Do they refer to the same point somehow??
    ActiveChart.SeriesCollection(1).Points(15).Select
    Selection.Format.Fill.ForeColor.RGB = RGB(226, 107, 10)
    ActiveChart.SeriesCollection(2).Points(15).Select
    Selection.Format.Fill.ForeColor.RGB = RGB(0, 107, 10)




I have uploaded the file to wetransfer if anyone wants to see it in action.
Excel file


Kind regards,
Domien

Is This A Good Question/Topic? 0
  • +

Replies To: Excel color graph points of multiple series collections

#2 Domien  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 29-September 10

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 05:52 AM

Anyone?
Was This Post Helpful? 0
  • +
  • -

#3 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,768
  • Joined: 26-March 09

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 06:15 AM

Not really used VBA, but the link below might help

http://stackoverflow...chart-excel-vba
Was This Post Helpful? 0
  • +
  • -

#4 Domien  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 29-September 10

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 06:25 AM

Hi,

Thank you.

That was one of the posts that helped me start this little project.
However it doesn't mention anything about multiple seriescollections..

For some reason it's not possible to show what properties a "point" has.
This is the Microsoft documentation on the Points collection (yes it's a collection... but of what)
http://msdn.microsof...e/ff835907.aspx


Thank you,
Domien
Was This Post Helpful? 0
  • +
  • -

#5 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,768
  • Joined: 26-March 09

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 06:53 AM

Which version of Excel are you running?
Was This Post Helpful? 0
  • +
  • -

#6 Domien  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 29-September 10

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 07:10 AM

On my laptop I'm running 2013, but I can code on a 2010 system if that would help with the problem.

I did have to create this file in a 2010 format, since the end user will be using 2010 and that doesn't support the new Pivottables.
2010 & 2013 have the same file extensions, but the files are slightly different in the background.

Thank you!
Was This Post Helpful? 0
  • +
  • -

#7 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,768
  • Joined: 26-March 09

Re: Excel color graph points of multiple series collections

Posted 03 April 2013 - 07:26 AM

I was only asking because I'm seeing different examples for different versions, so it's useful to know.

See if this helps.... http://www.get-digit...-in-charts-vba/

To be honest, I have no idea what you're expecting to see (like I said, I don't use Excel that much other than for basics).

It seems the points collection means different things depending on the chart type.

Download the example from the link and it seems to do what I think you want.....
Was This Post Helpful? 1
  • +
  • -

#8 Domien  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 29-September 10

Re: Excel color graph points of multiple series collections

Posted 04 April 2013 - 03:14 AM

Hi,

This link pointed me in the right direction.

Instead of using .Points(p).Format.Fill.Forecolor = ...
I had to use .Points(p).interior.color = ...

Not quite sure what's the difference though :)
But anyway, thank you !!

Domien


Link by maj3091
Link on the same site that had the solution

And my final code
'in the chart code
Private Sub Chart_Activate()
PivotChartChangeColors ("GRAFIEK")
End Sub

'in a separate module
'Domien De Clercq
'19/03/2013

Dim titleArrayNames As Collection

Sub PivotChartChangeColors(chartName As String)

On Error GoTo ErrHandler

Dim values As Variant
Dim sc As Long
Dim sc2 As Long
Dim pt As Long
Set titleArrayNames = New Collection

For sc = 1 To Charts(chartName).SeriesCollection.Count
    values = Charts(chartName).SeriesCollection(sc).values
    For pt = 1 To UBound(values)
        With Charts(chartName).SeriesCollection(sc)
        Dim v As Variant
        v = values(pt)
        addToChartTitle (.name)

            If v = 100 Then
                .Points(pt).Interior.Color = RGB(146, 208, 80)
            ElseIf v >= 90 Then
                .Points(pt).Interior.Color = RGB(226, 107, 10)
            ElseIf v < 90 And v > 1 Then
                .Points(pt).Interior.Color = RGB(255, 0, 0)
            ElseIf v = 1 Then
                .Points(pt).Interior.Color = RGB(217, 217, 217)
            ElseIf v = 0 Or v = Null Then
                .Points(pt).Interior.Color = RGB(0, 0, 0)
            End If
                                    
            If .Points(pt).Width > 12 Then
                .Points(pt).HasDataLabel = True
                .Points(pt).DataLabel.Position = 3
                
                Dim l As String
                l = getLabel(.name)
                
                If v < 100 Then
                     If v = 1 Then v = 0
                    .Points(pt).DataLabel.Text = l & vbCrLf & v
                Else
                    .Points(pt).DataLabel.Text = l
                End If
                
            Else
                .Points(pt).HasDataLabel = False
            End If
                        
     End With
    Next
Next

Charts(chartName).SetElement (msoElementChartTitleAboveChart)
Charts(chartName).ChartTitle.Text = getChartTitle
Exit Sub

ErrHandler:
MsgBox ("there was a problem updating the graph, try again or contact Technical IT")
End Sub

Private Function getLabel(name As String)
            If name = "Magazijn" Then
               name = "M"
            ElseIf name = "Preparatie" Then
                name = "P"
            End If
getLabel = name
End Function

Private Sub addToChartTitle(name As String)
Dim contains As Boolean
contains = False

For Each n In titleArrayNames
    If n = name Then contains = True
Next

If Not contains Then
    titleArrayNames.Add (name)
End If

End Sub

Private Function getChartTitle()

For Each Title In titleArrayNames
    If getChartTitle = "" Then
    getChartTitle = Title
    Else
    getChartTitle = getChartTitle + ", " + Title
    End If
Next

End Function



Was This Post Helpful? 0
  • +
  • -

#9 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,768
  • Joined: 26-March 09

Re: Excel color graph points of multiple series collections

Posted 04 April 2013 - 05:08 AM

Glad it sorted it....that's why I was asking about versions as some where doing it the way you did it and some using the interior colour and it also depending on the graph type.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3198
  • View blog
  • Posts: 10,732
  • Joined: 12-December 12

Re: Excel color graph points of multiple series collections

Posted 04 April 2013 - 05:32 AM

There is a similar discussion here:

http://social.msdn.m...f-7540225c6aa6/

It doesn't really clarify the distinction between .Format.Fill and .Interior, but I suppose it is reassuring to discover that you are not alone in encountering this issue :)

This post has been edited by andrewsw: 04 April 2013 - 05:33 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1