Excel VBA - Reference chart data point by name instead of index number

Question

I have a basic Gantt chart with a table as its data source, however, when another name is selected somewhere on the sheet, this table gets emptied and refilled with the data that belongs to the chosen name.

There is one data point the same for every name and I want its bar to have another color.

I know about this way to reference a data point:

ActiveChart.FullSeriesCollection(2).Points(3)

But that wont work in my example because the amount of data points keeps changing and the same thing is not always in the third position.

I tried this, but like I thought it throws me a type mismatch error:

ActiveChart.FullSeriesCollection(2).Points("SomeString")

Is it possible to reference a data point by its name in VBA?

Automate This · Accepted Answer · 2014-11-04 14:40:08Z

2

With is a simple Gantt chart that looks like this:

enter image description here

You can change the color of point B by first returning the XValue, match against the name your looking for and then set the corresponding point color:

Option Explicit

Sub ChangePointBColor()
    Dim x As Integer
    Dim varValues As Variant
    Dim cht As Chart
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 3").Chart

    With cht.SeriesCollection(2) 
        varValues = .XValues

        For x = LBound(varValues) To UBound(varValues)
            If varValues(x) = "B" Then
                .Points(x).Interior.Color = RGB(140, 125, 230)
            End If
        Next x
    End With
End Sub

Result

enter image description here

edited Nov 4, 2014 at 14:40

answered Nov 4, 2014 at 14:32

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Goos van den Bekerom Over a year ago

I use option explicit, should i define the varValues array as variant or string? I guess string, but just to make sure

Goos van den Bekerom Over a year ago

hehe thanks ;) btw, since varValues is an array it should be Dim varValues() As Variant right? or am I missing something?

Goos van den Bekerom Over a year ago

It worked almost perfect ;) when I selected a name that had it in first position for example, the next name I selected also got the first bar in the new color. But i fixed that by recoloring to the original color before the loop ;)

Automate This Over a year ago

Glad it worked well for your scenario. I believe () is implied for Variants, Option Explicit didn't throw an error for me but I'm not completely sure on how that is inferred.

Goos van den Bekerom Over a year ago

Ah oke I see, well learned another thing then haha ;) thank you for your help!

Collectives™ on Stack Overflow

Excel VBA - Reference chart data point by name instead of index number

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related