Workable Scatter Plot Data Points

  • Thread starter Thread starter Dave_AD
  • Start date Start date
D

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.
--
 
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
 
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.
 
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.
 
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
_______
 
Back
Top