Series Collection Points

[Bill]

Hello,
I would like to
select a single point on a chart, then select a button that will call the
following subroutine:

AddCommenttoPoint(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As
Long)

The elementID is XlSeries, Arg1 is the first series collection. The problem
I have is Arg2. I want that to be the data point that is selected on the
series. How do I determine which data point is selected on a series
collection using code? The Selected property does not work with the series
collection.

Thanks a lot.

Bill

 

[Tom Ogilvy]

Look at GetChartElement

 

[Bill]

Thanks Tom. But that doesn't work because I don't know x and y. I don't
want to have the macro in the workbook with the chart sheet, but that may be
the only way to do it.

Bill

 

[Tom Ogilvy]

In a line chart embedded in a worksheet, if I have a single point selected:

? typename(selection)
Point
? typename(selection.parent)
Series


If I have a series selected
? typename(selection)
Series

This seems to contradict your premis

 

[Bill]

Hello Tom,

You are correct (of course). But I still don't know what point it is, just
that it is a point that is selected. What am I missing?

Bill

 

[Tom Ogilvy]

What am I missing?

How to find out what point it is? <g>

seriously, what do you want to know. Which point (index) it is in the
points collection of the Series. Not to say I can tell you that, but I can
see if I can figure it out. (there doesn't appear to be anything inherent to
a point object that will tell you that.) I could always change an attribute
of the point, then loop through the points until I found the point with that
unique attribute, then change it back.

 

[Bill]

All I want to do is to look at a chart where there is a single data point
selected and find out which point is selected - without there being anything
special about the series collection. I am not sure it is possible.

Bill

 

[Tom Ogilvy]

Lets say I have 25 data points on a chart split between 5 series.

One data point is selected.

What do you mean by which data point. It doesn't have a name like Seymor,
so how are you identifying a data point.

My original suggestion would be akin to something like
SeriesCollection(2).Points(3)

as an example.

 

[Bill]

I may not be very clear on what I want. I want to find out which point is
selected without ever seeing the graph. You have one point selected on a
series collection. How can I find out which point that is using just VBA.

Thanks for responding. I do appreciate it.

 

[Tom Ogilvy]

It is the point represented by selection.

 

[Bill]

Hi Tom,
I found two routines that do what I wanted. One changes the color of the
point and then loops back through. Pretty clever. One uses an old Excel 4
Macro. Ran across your name a couple of times in the search. You have been
helping people for a while. Thanks.

Sub GoToPoint()
Application.ScreenUpdating = False
Dim I, lstatus, sNum As Integer, tempS As String
If TypeName(Selection) <> "Point" Then
MsgBox "You need to first select a point"
End
End If
sNum = Val(Right(Selection.Parent.Name, 1))
With ActiveChart.SeriesCollection(sNum)
lstatus = Selection.Border.ColorIndex
Selection.Border.ColorIndex = 2
For I = 1 To .Points.Count
If .Points(I).Border.ColorIndex = 2 Then Exit For
Next
End With
MsgBox I
'value of I is the index of the point in the series
End Sub

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 Peltier]

Bill -

Who cares what number point it is? Use the Selection object.

Sub AddCommentToPoint()
Dim sLabel As String
If TypeName(Selection) = "Point" Then
sLabel = Application.InputBox _
("Enter a label for the selected point", "Enter Label")
Selection.HasDataLabel = True
Selection.DataLabel.Text = sLabel
End If
End Sub

If you use GetChartElement, who cares what X and Y are? A mouse click is what gives
you X and Y, and you pass these to GetChartElement without caring about the values.
For an example, check the examples here:

http://peltiertech.com/Excel/Charts/chartvba.html#PointInfo

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

 

[Bill]

Thanks Jon. Like the website. The reason I want to know the point number
is more than adding a label. I want to remove it or add it from a series of
caculations as well. So, you can select a point, hit a button and a dialog
box with several options - including adding a label, but also removing the
point from the calculations.

Bill