How to find index # of selected chart point

[snovotny]

My goal is to readjust my chart for specific viewing. I want to select a particular point, then press a macro button, and, by having the index number of that point, I can adjust the relevant scrollers (I have one for position, and one for "compression"), so that I can see an expanded view of the area of the chart that contains the point I selected.

I simply need to assign the selected point's index number to a variable.

Here's a macro I recorded of selecting the point:

Sub Macro1()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(7).Points(4176).Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub


So, to try a test I wrote the following macro--which tells me "the object isn't supported" (or something like that). (Note, I put "Stop" so that I can just see if my variable has caught the value. So, I select the point, press the macro button, and I get the error):

Sub MacroTest()
Dim IsIndex As Integer
With Selection
IsIndex = .Index
Stop
End With
End Sub

I simply don't know how to ask for the point index number.

Appreciate any help!
Thanks!

 

[Sal Paradise]

Try this in a chart object's sheet:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
    Debug.Print ElementID
End Sub

That will give you the point number.

 

[snovotny]

Thanks. I just want to get it into a variable to do further work. So could I do:

With Selection
MyVariable = .ElementID
End With

??

 

[Sal Paradise]

As far as I can tell, you can't use selection. You need to use the Selection Change event to note the ElementID of the object selected.

 

[snovotny]

Really could use more help with this one.

I want to select a point on a chart (an embedded chart, btw), run a macro, and that macro can identify the index number of that point, and pass it to a variable.

The rest I know how I want to accomplish--using that index number to reset scrollers so that I can have an exploded view of that area of the chart.

Manually adjusting scrollers is tedious (I need to look at highly "compacted" charts, switch to an "exploded" view, then back again--over and over). If there's another way besides my idea, I'd be happy to hear it. FYI: I'm looking at about 90K data points; I look at a series of 6K points, pick an area of interest, and then wish to look at about 300 points in that area. I can only see what I want to see at the 6K view, and then NEED to analyze further at the 0.3K view. And I need to do it A LOT--scrolling becomes tedious.

 

[Sal Paradise]

Yet again, let me restate:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
    Debug.Print ElementID
End Sub

This code will give you the element ID whenever you select an element of a chart.

That is what you asked for.

You cannot use Selection in a macro to do the same as far as I can tell.

If you want to pass this to another function for use in a macro of some sort, just declare a global variable that holds the ElementID whenever you select something, and then run whatever code you want on it.

 

[roscoe]

Try this in a chart object's sheet:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
    Debug.Print ElementID
End Sub

That will give you the point number.

Sorry to dreg up an old post, but I have a similar question, wanting to extract the xvalue from a selected point. You say to "Try this in a chart object's sheet" but not sure what that means with an imbedded chart. Rather than piecewise the solution, could someone step through a simple VBA macro (and any required additional code) that would extract the x-value from a selected datapoint?

Thanks!