Can we return source data address from chart object?

[Atroxell]

hi all,

I just got asked a stumper...I know it won't stump all of you for long.

One of my coworkers has a nasty workbook and chart that someone else built. The chart does not actually contain labels that are immediately accessible by the series definitions, even though they are in the source workbook. Even though the layout of the data does not allow for a standard series definition for the labels, there is enough regularity that we think we might be able to automate it to a certain degree so that these labels, which are currently updated manually on a weekly basis, can be updated via a macro. (We did not build this workbook--It's another "inherited workbook" and would cause an uproar from bottom to top if we tried to change the report in any way...)

Anyway, this person is looking for a way to automate the redefining of the text labels in the chart, so he's looking for a way to return the cell address of a text label in the chart object. Once that's is done, he can update the label using a loop of some kind and looping through the Series collection data points and, once he has an address, using an offset method or somesuch to return a value. This would save A LOT of time each week.

I have tried using a recorded macro and managed to get close, but I can't find a way to return the cell address of the label.

Any Suggestions?

Here's what I was able to record, and I can easily change the label by re-defining the cell address I want it to represent. (Which I have already done by replacing the value with a cell reference in this example.)

Sub Test()
        ActiveSheet.ChartObjects("Chart 8").Activate
        ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
        ActiveSheet.ChartObjects("Chart 8").Activate
        ActiveChart.SeriesCollection(1).Points(3).DataLabel.Text = Cells(4, 60)                            '"$1,045.33"
End Sub

Hope this is clear!

 

[Atroxell]

Anyone?

Any help would be appreciated.

 

[Atroxell]

Do I get a prize for stumping the board?

I guess there's a first time for everything...