ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Workable Scatter Plot Data Points (https://www.excelbanter.com/excel-programming/403415-workable-scatter-plot-data-points.html)

Dave_AD
Workable Scatter Plot Data Points
 
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
Workable Scatter Plot Data Points
 
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



"Dave_AD" wrote in message
...
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
Workable Scatter Plot Data Points
 
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.


"Peter T" <peter_t@discussions wrote in message
...
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



"Dave_AD" wrote in message
...
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.
--







Dave_AD
Workable Scatter Plot Data Points
 
Looks great! Thanks for all your help
--
Dave_DD


"Peter T" wrote:

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.


"Peter T" <peter_t@discussions wrote in message
...
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



"Dave_AD" wrote in message
...
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.
--








Dave_AD
Workable Scatter Plot Data Points
 
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_DD


"Dave_AD" wrote:

Looks great! Thanks for all your help
--
Dave_DD


"Peter T" wrote:

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.


"Peter T" <peter_t@discussions wrote in message
...
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



"Dave_AD" wrote in message
...
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.
--








Jon Peltier
Workable Scatter Plot Data Points
 
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
_______



"Dave_AD" wrote in message
...
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_DD


"Dave_AD" wrote:

Looks great! Thanks for all your help
--
Dave_DD


"Peter T" wrote:

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.


"Peter T" <peter_t@discussions wrote in message
...
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



"Dave_AD" wrote in message
...
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.
--











All times are GMT +1. The time now is 12:35 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com