Linking to a Charts Source Data? 1

[MatthewBell]

Hi Helpful Members

I have a number of spreadsheets with masses of embedded charts on them. The source data for these charts is in the same workbook but on a different sheet.

I would ideally like to make it so that when the user selects (clicks on) a series in a chart it takes them to the source data i.e. selects the source data for that series.

I've been finding this very difficult and cannot find a Chart Event for selecting a SeriesCollection let alone write code to find and select the source data.

If this is not possible I would be happy if it at least took the user to the column used by the chart (All data used by any one chart is within a single column).

Oh but the source data may not stay in the same place and there really are loads of charts so its not suitable to put direct links to each column from each chart i.e. a different piece of code for each chart.

This must be possible. Right?

Many Thanks for your time

Matt

 

[xlbo]

AFAIK, there is no event for selecting a series, however, if you have a series selected, this will take you to the top of the set of values for that series:

sub TakeMeHome()
Dim selSeries As String, mStr As String, pos1 As Integer, pos2 As Integer, pos3 As Integer
Dim mSht As String, mRng As String
selSeries = Selection.Name
mStr = ActiveChart.SeriesCollection(selSeries).Formula
pos1 = InStr(1, mStr, "(&quot + 1
pos2 = InStr(1, mStr, "!&quot
pos3 = InStr(1, mStr, ",&quot
mSht = Mid(mStr, pos1, pos2 - pos1)
mRng = Mid(mStr, pos2 + 1, pos3 - pos2 - 1)
Sheets(mSht).Select
Range(mRng).Select
end sub

This could be done with less steps but I thought that this would explain the workings better Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur