linking data point on chart to cell

[jcg31]

I have a scatter chart that is displaying a range of data on worksheet ("Data"). I want to be able to select a point displayed on the chart and have the corresponding cell on the worksheet ("Data") to become selected.
Would seem possible (given the link created on the chart to the data) but I can't find anything that gets me there.

Any help would be appreciated.
Jim

 

[pgc01]

Hi Jim

To do it you have to use the chart events, in this case you can

- define 2 named ranges, for the X and Y values
- use the Select event to hightlight the series or a point in the series
- use the Deactivate event to kill all the highlighting when you leave the chart

If the chart is in a chart sheet the events are available by default. If the chart is embedded in a worksheet you have to create a class to make the chart events available.

The help explains how to do it. Check the help on the chart events and also the help section "Using Events with Embedded Charts"

Remark: you should always post your excel version. An easy way not to forget it is to include it in your signature.

 

[jcg31]

Using office 2003

Found my way through, well kinda
Created a Chart Class and then in ThisWorkbook I placed the following code:

 Dim ChartObjectClass As New ChartEventClass


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 On Error Resume Next
 Set ChartObjectClass.ChartObject = Worksheets("Summary").ChartObjects(1).Chart

End Sub

My new question is how do I change this code so the event affects all the ChartObjects not just ChartObject(1).

Thanks,
Jim