HELP need index of the actual selected point

[Schlupp]

Hi,

I need help. I must get the index of a the currently selectd point in
the chart. I can't use the Mouseup event.
It will be better to determinate the x and y value from the actual
selected point but the index will also be good.

Please help!

thx
Jens

[Jon Peltier]

You need to use an old-style XLM command for this, as Stephen Bullen
pointed out in his post 14-Jan-2001:

Sub WhichPoint()

Dim sPoint As String
Dim iSeries As Integer, iPoint As Integer

sPoint = ExecuteExcel4Macro("SELECTION()")

If sPoint Like "S*P*" Then
iSeries = Val(Mid$(sPoint, 2))
iPoint = Val(Mid$(sPoint, Len(iSeries) + 2))

MsgBox "Series " & iSeries & ", Point " & iPoint
Else
MsgBox "Please select a single data point."
End If

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Schlupp wrote:

> Hi,
>
> I need help. I must get the index of a the currently selectd point in
> the chart. I can't use the Mouseup event.
> It will be better to determinate the x and y value from the actual
> selected point but the index will also be good.
>
> Please help!
>
> thx
> Jens
>

[Ed Ferrero]

Hi Jon,

There is also this old post from Eric Wells, if you do not like to use XLM.
<quote>
'>>>>There is no easy way, as there is no name property for the Point
'object. Below you will find a function that will return a string
'corresponding to the point designation you see in the dropdown name box
'on a chart sheet (I've also included a macro that calls the function).
'The function relies on setting the MarkerStyle property of the point
'and then using a For-Next loop to identify the point index and then
'resetting the markerstyle. Note that there is no error checking to
'make sure that a point is actually selected, nor to check to see if the
'proper marketstyle is being used (if the entire series is formatted as
'xlstar, the funciton will fail - you'll have to select a different
'markerstyle).

Sub GetPoint()
Dim PointObject As Object
Set PointObject = Selection
MsgBox ReturnPoint(PointObject)
End Sub

Function ReturnPoint(PointObject As Object) As String
Dim PointMarkerStyle As Variant
Dim SeriesNum As Integer
Dim PointNum As Integer
Dim x As Integer
PointMarkerStyle = PointObject.MarkerStyle
PointObject.MarkerStyle = xlStar
With ActiveChart.SeriesCollection(PointObject.Parent.Name)
SeriesNum = .PlotOrder
For x = 1 To .Points.Count
If .Points(x).MarkerStyle = xlStar Then
PointNum = x
Exit For
End If
Next
End With
PointObject.MarkerStyle = PointMarkerStyle
ReturnPoint = "S" & SeriesNum & "P" & PointNum
End Function

'-Eric Wells
'Microsoft
</quote>

Ed Ferrero
http://edferrero.m6.net/



> You need to use an old-style XLM command for this, as Stephen Bullen
> pointed out in his post 14-Jan-2001:
>
> Sub WhichPoint()
>
> Dim sPoint As String
> Dim iSeries As Integer, iPoint As Integer
>
> sPoint = ExecuteExcel4Macro("SELECTION()")
>
> If sPoint Like "S*P*" Then
> iSeries = Val(Mid$(sPoint, 2))
> iPoint = Val(Mid$(sPoint, Len(iSeries) + 2))
>
> MsgBox "Series " & iSeries & ", Point " & iPoint
> Else
> MsgBox "Please select a single data point."
> End If
>
> End Sub
>
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Schlupp wrote:
>
>> Hi,
>>
>> I need help. I must get the index of a the currently selectd point in
>> the chart. I can't use the Mouseup event.
>> It will be better to determinate the x and y value from the actual
>> selected point but the index will also be good.
>>
>> Please help!
>>
>> thx
>> Jens
>>

[Jon Peltier]

Hi Ed -

I also had a version of the Eric Wells approach, with temporary point
formatting, but didn't suggest it because I'm not comfortable with it.
My problem, alluded to in Eric's post, is that if my series formatting
is already xlStar, I'll never find the selected point. This can be fixed
using another layer of coding, but it was already longer and more
convoluted than the XLM command. And the XLM command detects whether a
point is selected (If sPoint Like "S*P*" Then).

XLM's not so bad, and often (like this) it's the easiest way, or the
only way, to get something done. Print Setup is another: the VBA way
takes minutes, while XLM is nearly instantaneous.

Many times people do things the hard way, because they have a
preconceived notion they force their approach to conform to. Like it's
bad to add columns for intermediate calculations or for conditioned data
for charting. In fact, it's easier to do it this way, and you can find
your mistakes more easily. And you can put the extra range out of sight
of the casual observer.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Ed Ferrero wrote:
> Hi Jon,
>
> There is also this old post from Eric Wells, if you do not like to use XLM.
> <quote>
> '>>>>There is no easy way, as there is no name property for the Point
> 'object. Below you will find a function that will return a string
> 'corresponding to the point designation you see in the dropdown name box
> 'on a chart sheet (I've also included a macro that calls the function).
> 'The function relies on setting the MarkerStyle property of the point
> 'and then using a For-Next loop to identify the point index and then
> 'resetting the markerstyle. Note that there is no error checking to
> 'make sure that a point is actually selected, nor to check to see if the
> 'proper marketstyle is being used (if the entire series is formatted as
> 'xlstar, the funciton will fail - you'll have to select a different
> 'markerstyle).
>
> Sub GetPoint()
> Dim PointObject As Object
> Set PointObject = Selection
> MsgBox ReturnPoint(PointObject)
> End Sub
>
> Function ReturnPoint(PointObject As Object) As String
> Dim PointMarkerStyle As Variant
> Dim SeriesNum As Integer
> Dim PointNum As Integer
> Dim x As Integer
> PointMarkerStyle = PointObject.MarkerStyle
> PointObject.MarkerStyle = xlStar
> With ActiveChart.SeriesCollection(PointObject.Parent.Name)
> SeriesNum = .PlotOrder
> For x = 1 To .Points.Count
> If .Points(x).MarkerStyle = xlStar Then
> PointNum = x
> Exit For
> End If
> Next
> End With
> PointObject.MarkerStyle = PointMarkerStyle
> ReturnPoint = "S" & SeriesNum & "P" & PointNum
> End Function
>
> '-Eric Wells
> 'Microsoft
> </quote>
>
> Ed Ferrero
> http://edferrero.m6.net/
>
>
>
>
>>You need to use an old-style XLM command for this, as Stephen Bullen
>>pointed out in his post 14-Jan-2001:
>>
>>Sub WhichPoint()
>>
>> Dim sPoint As String
>> Dim iSeries As Integer, iPoint As Integer
>>
>> sPoint = ExecuteExcel4Macro("SELECTION()")
>>
>> If sPoint Like "S*P*" Then
>> iSeries = Val(Mid$(sPoint, 2))
>> iPoint = Val(Mid$(sPoint, Len(iSeries) + 2))
>>
>> MsgBox "Series " & iSeries & ", Point " & iPoint
>> Else
>> MsgBox "Please select a single data point."
>> End If
>>
>>End Sub
>>
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Schlupp wrote:
>>
>>
>>>Hi,
>>>
>>>I need help. I must get the index of a the currently selectd point in
>>>the chart. I can't use the Mouseup event.
>>>It will be better to determinate the x and y value from the actual
>>>selected point but the index will also be good.
>>>
>>>Please help!
>>>
>>>thx
>>>Jens
>>>
>
>
>

[Schlupp]

Thats what I needed. Thankyou very much.

Jens