Workable Scatter Plot Data Points

[Dave_AD]

Does anybody have a routine to turn scatter plot points "on" or "off" on a
chart using, say, a mouse action, that would grab the point and put it into
a second series, thereby changing the color and/or symbol? This would be a
great benefit to removing outliers when adding trendlines.

I've tried writing a little VBA , but didn't get anywhere since I can't seem
to get Excel to identify the X-Y point locations outside of the actual values
(row-column would be better) which, if you deal with 10,000+ pairs doesn't
help much.

Thanks for any help in advance.
--

 

[Peter T]

Following worked for me set up as follows

Y values for the main and first series in cells named "Main".
Y values of a second dummy series in cells named "Out", same size.

Add a class module

' code in a class named Class1
Option Explicit
Public WithEvents cht As Excel.Chart
Dim mnPnt As Long
Dim mnSeries As Long

Private Sub cht_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
If mnPnt Then
If mnSeries = 2 Then
Range("Main")(mnPnt) = Range("Out")(mnPnt)
Range("out")(mnPnt).Clear
ElseIf mnSeries = 1 Then
Range("Out")(mnPnt) = Range("Main")(mnPnt)
Range("Main")(mnPnt).Clear
End If
End If

mnPnt = 0
mnSeries = 0
End Sub

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

mnPnt = 0
mnSeries = 0

If ElementID = xlSeries And Arg2 > 0 Then
mnSeries = Arg1
mnPnt = Arg2
End If

End Sub

' code in a normal module
Dim clsChtEvents As Class1

Sub StartChartEvents()
Set clsChtEvents = New Class1

Set clsChtEvents.cht = ActiveSheet.ChartObjects(1).Chart

End Sub

Sub StopChartEvents()
Set clsChtEvents = Nothing
End Sub

Run StartChartEvents. Select individual points in either series to
toggle/move into the other series. If all was working then it stops working,
probably due to clsChtEvents going out of scope due to editing code, re-run
StartChartEvents.

This of course assumes a chartobject. If a chart sheet much simpler, just
the code the two similarly named events behind the chart-sheet.

Regards,
Peter T

 

[Peter T]

On reflection I made that more complicated than it need be, I think simply
this should be OK:

' code in a class named Class1
Option Explicit
Public WithEvents cht As Excel.Chart

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

If ElementID = xlSeries And Arg2 > 0 Then
If Arg1 = 1 Then
Range("Out")(Arg2) = Range("Main")(Arg2)
Range("Main")(Arg2).Clear
ElseIf Arg1 = 2 Then
Range("Main")(Arg2) = Range("Out")(Arg2)
Range("out")(Arg2).Clear
End If
' following optional
On Error Resume Next ' error if no value points
cht.SeriesCollection(Arg1).Select
On Error GoTo 0
End If

End Sub


and as before -

' code in a normal module
Dim clsChtEvents As Class1
Sub StartChartEvents()
Set clsChtEvents = New Class1

Set clsChtEvents.cht = ActiveSheet.ChartObjects(1).Chart

End Sub

Sub StopChartEvents()
Set clsChtEvents = Nothing
End Sub

Regards,
Peter T

PS I had posted the above by mistake into another thread yesterday.

 

[Dave_AD]

Looks great! Thanks for all your help

 

[Dave_AD]

I tried to get this procedure to work by creating a simple scatter plot and
running the subroutine as per your suggestions. How do you select the points
on the plot? Nothing seems to work. Any help would be great. Thanks.

 

[Jon Peltier]

Dave -

You need to create a class module, name it Class1, and insert the code Peter
supplied:

'---- class module
Option Explicit
Public WithEvents cht As Excel.Chart

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

If ElementID = xlSeries And Arg2 > 0 Then
If Arg1 = 1 Then
Range("Out")(Arg2) = Range("Main")(Arg2)
Range("Main")(Arg2).Clear
ElseIf Arg1 = 2 Then
Range("Main")(Arg2) = Range("Out")(Arg2)
Range("out")(Arg2).Clear
End If
' following optional
On Error Resume Next ' error if no value points
cht.SeriesCollection(Arg1).Select
On Error GoTo 0
End If

End Sub
'---- end of class module

Then you need a regular code module with Peter's other code:

'---- regular module
Option Explicit
Dim clsChtEvents As Class1

Sub StartChartEvents()
Set clsChtEvents = New Class1
Set clsChtEvents.cht = ActiveSheet.ChartObjects(1).Chart
End Sub

Sub StopChartEvents()
Set clsChtEvents = Nothing
End Sub
'---- end of regular module

When you're ready to play with the chart, run the StartChartEvents
procedure, and when you're finished, run StopChartEvents.

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