Chart Point Label with Variable Info

[Cyberdude]

I can (and do) create titles for charts that contain variable information. When I create the title box, I simply give the address of where the information is on the active worksheet. When that information changes, the info in the chart title changes correspondingly.

I am now trying to do the same thing with a point label on the chart. I wrote a macro that creates a Series consisting of a single point, then it creates a label for that point, then it provides the address of the info I want to be in the label. It works just fine.

My problem is that the info I want to be in the label is variable. When certain values on the chart change, I want the value in the label to change. I try to accomplish this magic by having a formula in the cell where the label gets its value. When I first create the label, it does display the information from the cell with the formula. However, when the formula produces a different value in the cell on the worksheet, the label on the chart does not change.

Is this the way it is supposed to work, i.e, once a chart label value is created, the label will retain that value forever and not respond to changes in the cell it is linked to?

[HaHoBe]

Hi, Cyberdude,

to my knowledge these values are static indeed (maybe the solution will be posted within the next answer ;)). I work around that using the Worksheet_Change event in a given range to trigger the macro once a change has been noticed.

Ciao,
Holger

[ALe]

I agree with HaHoBe. As far as I know you must run a procedure linked to an event

[Cyberdude]

Thanx, fellas. I can live with that solution. I wonder why Microsoft omitted that feature?? Oh, well, back to the drawing board ...
Cheers!
Sid

[Andy Pope]

Hi,

You can create a dynamic link between data labels and cells.
Sub X()

Dim rngLabels As Range
Dim lngPoint As Long

Set rngLabels = Range("D2:D6")
With ActiveChart.SeriesCollection(1)
.HasDataLabels = True
For lngPoint = 1 To .Points.Count
.Points(lngPoint).DataLabel.Text = "='" & rngLabels.Parent.Name & "'!" & rngLabels.Cells(lngPoint).Address(ReferenceStyle:=xlR1C1)
Next
End With

End Sub

[Cyberdude]

Andy, in effect that's the code that I use, except I have the special case that there is just one point in the collection. I have no trouble creating the dynamic link, but the result is static. Said differently, once the label is created by copying the info from the referenced cell, if the value in the cell changes, the value in the label doesn't change correspondingly. But thanks for the suggestion.

[Andy Pope]

So does my attached workbook not work as you want?

Just press F9 to change all the random values which in turn will change the labels.

This code mod does the series collections with a single point Dim rngLabels As Range
Dim lngSeries As Long

Set rngLabels = Range("D2:D6")
With ActiveChart
For lngSeries = 1 To .SeriesCollection.Count
.SeriesCollection(lngSeries).HasDataLabels = True
.SeriesCollection(lngSeries).Points(1).DataLabel.Text = "='" & rngLabels.Parent.Name & "'!" & rngLabels.Cells(lngSeries).Address(ReferenceStyle:=xlR1C1)
Next
End With

[Cyberdude]

Oh, Man, I didn't pursue it that far because it looked like what I have. :blush I'll spend some more time on it. Right now I'm fighting "Not Enough Resources to Display" messages.
If it's not one thing, it's another. Always something to divert me into another exploration. It's driving me cookieputz! :soupbox: