click data point in chart and delete

does anyone hava a routine for clicking a data point in a chart and
deleting this point either from the series or from the worksheet it's
being referenced from?

thanks.

smokii

I don't have such a routine. At this point, it doesn't look like anyone else
has either. My thoughts on the subject follow.

Simple approach:

If you're unaware, you might want to consider Excel's existing point
manipulation feature:
i) Click on any point in the series to select the series.
ii) Then click the desired point to select it. The mouse pointer should
change into either a four-way or up-down arrow if done correctly.
iii) Drag the point to the desired value. You can't to my knowledge delete
it however. But you can drag it so that it's in line with the preceeding and
following points in the series thus nullifying its affect on trendlines etc.

The above assumes that the points in the series are simple values as opposed
to formula results. In the latter case, you will likely need a routine to
copy the formula results (as values) to the plot range so that the chart
links only to these values.

Complex approach:

If the above "Simple approach" is not acceptable then I think you would be
looking at creating a Class module and, using the Mouse_Down event and
GetChartElement function, determining the point clicked on, and have code
take it from there.

I think my approach would be to have three ranges:
1) The first holds the x-values
2) The second holds all the original y-values
3) The third holds the plotted y-vlaues.

At the start, the second and third ranges would both contain all the y-data.
When you click on the desired point, GetChartElement returns the selected
series index value (arg. a) and the selected point index value (arg. b). Code
would then clear the y-value from the third range corresponding with the
selected point using the index value as an offset. After deleting all the
desired points (clearing the cells in the third range) you could have a reset
function that repopulates the third range using the second as the source.

However, this should be OK so long as there arn't too many points involved.
If there are several, and if you change your mind or accidently click the
wrong point, then resetting and starting from scratch might be unacceptable.

An alternate approach would be to actually have two series: The first series
plots all the points (i.e. the above 2nd range). This series would not plot a
line, just point markers. In contrast, the second series would not plot point
markers but instead would plot the line.

When you click on a point marker from the first series, the point marker
would toggle from a filled condition (indicating a plotted state) to a
non-filled condition (indicating an unplotted state). GetChartElement would
then identify the offset for the clicked point and either clear or repopulate
the cell from the third (plotted) range depending on whether the clicked
point marker was filled or non-filled.

Most likely, within 2 nonoseconds or so, someone will follow my post with a
fully working routine perfect for your needs.

Best regards,
Greg



You can drag it so that it's in line with the preceeding and following
points therefore nullifying its affect.

"[email protected]" wrote:

> does anyone hava a routine for clicking a data point in a chart and
> deleting this point either from the series or from the worksheet it's
> being referenced from?
>
> thanks.
>
> smokii
>
>

If you're still interested (and monitoring this post), then I put together a
macro that demos what I discussed. It creates 3 dynamic named ranges plus a
demo chart with two series. The first series displays only point markers (no
line) while the second series displays only a line plot (no point markers).
It allows you to click on points to toggle their exclusion/inclusion from the
line plot.

If a point marker from the first series is filled
(MarkerBackgroundColorIndex > 0) then clicking on it clears its fill
(MarkerBackgroundColorIndex = xlNone) and deletes the associated y-value from
the line plot. If the point marker is not filled then clicking it reinstates
the fill and the associated y-value is added to the line plot.

You have to hold down the <Alt> button while clicking for it to work. Else,
it's a huge nuissance. The macro is designed to work with an embedded chart
but can be rigged to work with a chart sheet.

Regards,
Greg

I'd be very abliged. Tahnks for your input.

I'd be very abliged. Thanks for your input.

For the record, this is just a demo that accomplishes what I had suggested as
a means to toggle deletion/inclusion of points from an XYScatter plot. It is
here offered as requested by [email protected].

Instructions:

1) The demo is based on an XYScatter plot. The data must consist of three
columns:
i) The left column holds the x-values
ii) The middle column holds the y-values
iii) The right column holds duplicate y-values
2) Select the cell at the top-left corner of the data range (first x-value)
before running the macro.
3) Create a Class module and paste the following code. It is assumed that it
is named Class1.

Option Explicit
Public WithEvents EmbedChart As Excel.Chart
Private Sub EmbedChart_MouseDown(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ID As Long, a As Long, b As Long
Application.ScreenUpdating = False
If GetKeyState(VK_ALT) >= 0 Then Exit Sub
With Me.EmbedChart
.GetChartElement x, y, ID, a, b
If b = 0 Then Exit Sub
With .SeriesCollection(2).Points(b)
Select Case .MarkerBackgroundColorIndex
Case Is > 0
.MarkerBackgroundColorIndex = xlNone
Range("YPlotRng")(b).ClearContents
Case Else
.MarkerBackgroundColorIndex = 3
Range("YPlotRng")(b) = Range("YRng")(b)
End Select
End With
End With
Windows(ActiveWorkbook.Name).RangeSelection.Select
End Sub

4) Paste the following code to a standard module.
5) Run the macro.
6) The linkage between the chart and the Class1 class will have to be reset
each time the workbook is opened (i.e. rerun SetMyChart macro).

The macro will create the chart and provide a siimple instructions message.
Note that this is just a demo and will not likely be suitable for your
situation as is. Your situation may only require one plotted series and a
simple Reset button if there are only a few points to delete.

Regards,
Greg

Option Explicit
Public Declare Function GetKeyState Lib "user32" _
(ByVal NVirtKey As Long) As Integer
Public Const VK_ALT As Integer = &H12
Dim MyChart As New Class1

Sub SetMyChart()
Set MyChart.EmbedChart = ActiveSheet.ChartObjects _
("Toggle Point Deletion Test").Chart
End Sub
Sub TogglePointInclusionDemo()
Dim ws As Worksheet, wb As Workbook
Dim chtobj As ChartObject
Dim cht As Chart
Dim s As Series
Dim txt As String
Dim c As Range
Dim Resp As Integer
Dim W As Single, H As Single


txt = "Set-up Instructions:" & vbCr & vbCr & _
"The chart data range must consist of three columns constructed " & _
"as follows:" & _
vbCr & "i) The first column must contain the x-values" & _
vbCr & "ii) The second column must contain the y-values" & _
vbCr & "iii) The third column must contain duplicate y-values" & _
vbCr & vbCr & "Before running the macro, the top-left cell of " & _
"the data range (first x-value) must be selected. If it is not " & _
"the currently active cell then click Cancel, select the cell and " & _
"run the macro again."
Resp = MsgBox(txt, vbInformation + vbOKCancel, "Chart Set-up")
If Resp = vbCancel Then Exit Sub

Set ws = ActiveSheet
Set wb = ActiveWorkbook
On Error Resume Next
ws.ChartObjects("Toggle Point Deletion Test").Delete
Set c = ActiveCell
txt = "=Offset(" & ws.Name & "!" & c.Address & _
", 0, 0, Count(" & c.EntireColumn.Address & "), 1)"
With ThisWorkbook.Names
.Add "XRng", RefersTo:=txt
txt = "=Offset(XRng, 0, 1)"
.Add "YRng", RefersTo:=txt
txt = "=Offset(XRng, 0, 2)"
.Add "YPlotRng", RefersTo:=txt
End With
On Error GoTo 0
W = ActiveWindow.VisibleRange.Width - 100
H = ActiveWindow.VisibleRange.Height - 100
Set chtobj = ws.ChartObjects.Add(50, 50, W, H)
chtobj.Name = "Toggle Point Deletion Test"
Set cht = chtobj.Chart
cht.HasLegend = False
cht.ChartType = xlXYScatter
cht.DisplayBlanksAs = xlInterpolated
Set s = cht.SeriesCollection.NewSeries
s.XValues = "=" & wb.Name & "!XRng"
s.Values = "=" & wb.Name & "!YPlotRng"
s.MarkerStyle = xlMarkerStyleNone
s.Border.Color = vbBlue
Set s = cht.SeriesCollection.NewSeries
s.XValues = "=" & wb.Name & "!XRng"
s.Values = "=" & wb.Name & "!YRng"
s.MarkerStyle = xlMarkerStyleSquare
s.MarkerSize = 6
s.MarkerBackgroundColorIndex = 3
s.MarkerForegroundColorIndex = 3
Call SetMyChart
c.Select
txt = "Operation Instructions:" & vbCr & vbCr & _
"1) Hold down the <Alt> key and click the desired points to toggle " & _
"their exclusion/inclusion from the line plot." & vbCr & _
"2) Don't click the points too quickly in succession or this will " & _
"activate the Format Object dialog." & vbCr & _
"3) If you accidently activate the Format Object dialog then close " & _
"it and activate the worksheet before continuing." & vbCr & _
"4) You need to reset the chart to the Class1 class (i.e. run SetMyChart) "
& _
"each time you open the workbook. Suggested is that you use the " & _
"Workbook_Open event to accomplish this."
MsgBox txt, vbInformation, "Chart Set-up"
End Sub


"[email protected]" wrote:

> I'd be very abliged. Thanks for your input.
>
>

Further to my post, the macro also adds three dynamic named ranges. These
allow it to automatically adapt to changes in both the x- and y-data ranges.
You might want to delete these names from your project (Insert>Name>Define)
afterwards.

"Greg Wilson" wrote:

> For the record, this is just a demo that accomplishes what I had suggested as
> a means to toggle deletion/inclusion of points from an XYScatter plot. It is
> here offered as requested by [email protected].
>
> Instructions:
>
> 1) The demo is based on an XYScatter plot. The data must consist of three
> columns:
> i) The left column holds the x-values
> ii) The middle column holds the y-values
> iii) The right column holds duplicate y-values
> 2) Select the cell at the top-left corner of the data range (first x-value)
> before running the macro.
> 3) Create a Class module and paste the following code. It is assumed that it
> is named Class1.
>
> Option Explicit
> Public WithEvents EmbedChart As Excel.Chart
> Private Sub EmbedChart_MouseDown(ByVal Button As Long, _
> ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
> Dim ID As Long, a As Long, b As Long
> Application.ScreenUpdating = False
> If GetKeyState(VK_ALT) >= 0 Then Exit Sub
> With Me.EmbedChart
> .GetChartElement x, y, ID, a, b
> If b = 0 Then Exit Sub
> With .SeriesCollection(2).Points(b)
> Select Case .MarkerBackgroundColorIndex
> Case Is > 0
> .MarkerBackgroundColorIndex = xlNone
> Range("YPlotRng")(b).ClearContents
> Case Else
> .MarkerBackgroundColorIndex = 3
> Range("YPlotRng")(b) = Range("YRng")(b)
> End Select
> End With
> End With
> Windows(ActiveWorkbook.Name).RangeSelection.Select
> End Sub
>
> 4) Paste the following code to a standard module.
> 5) Run the macro.
> 6) The linkage between the chart and the Class1 class will have to be reset
> each time the workbook is opened (i.e. rerun SetMyChart macro).
>
> The macro will create the chart and provide a siimple instructions message.
> Note that this is just a demo and will not likely be suitable for your
> situation as is. Your situation may only require one plotted series and a
> simple Reset button if there are only a few points to delete.
>
> Regards,
> Greg
>
> Option Explicit
> Public Declare Function GetKeyState Lib "user32" _
> (ByVal NVirtKey As Long) As Integer
> Public Const VK_ALT As Integer = &H12
> Dim MyChart As New Class1
>
> Sub SetMyChart()
> Set MyChart.EmbedChart = ActiveSheet.ChartObjects _
> ("Toggle Point Deletion Test").Chart
> End Sub
> Sub TogglePointInclusionDemo()
> Dim ws As Worksheet, wb As Workbook
> Dim chtobj As ChartObject
> Dim cht As Chart
> Dim s As Series
> Dim txt As String
> Dim c As Range
> Dim Resp As Integer
> Dim W As Single, H As Single
>
>
> txt = "Set-up Instructions:" & vbCr & vbCr & _
> "The chart data range must consist of three columns constructed " & _
> "as follows:" & _
> vbCr & "i) The first column must contain the x-values" & _
> vbCr & "ii) The second column must contain the y-values" & _
> vbCr & "iii) The third column must contain duplicate y-values" & _
> vbCr & vbCr & "Before running the macro, the top-left cell of " & _
> "the data range (first x-value) must be selected. If it is not " & _
> "the currently active cell then click Cancel, select the cell and " & _
> "run the macro again."
> Resp = MsgBox(txt, vbInformation + vbOKCancel, "Chart Set-up")
> If Resp = vbCancel Then Exit Sub
>
> Set ws = ActiveSheet
> Set wb = ActiveWorkbook
> On Error Resume Next
> ws.ChartObjects("Toggle Point Deletion Test").Delete
> Set c = ActiveCell
> txt = "=Offset(" & ws.Name & "!" & c.Address & _
> ", 0, 0, Count(" & c.EntireColumn.Address & "), 1)"
> With ThisWorkbook.Names
> .Add "XRng", RefersTo:=txt
> txt = "=Offset(XRng, 0, 1)"
> .Add "YRng", RefersTo:=txt
> txt = "=Offset(XRng, 0, 2)"
> .Add "YPlotRng", RefersTo:=txt
> End With
> On Error GoTo 0
> W = ActiveWindow.VisibleRange.Width - 100
> H = ActiveWindow.VisibleRange.Height - 100
> Set chtobj = ws.ChartObjects.Add(50, 50, W, H)
> chtobj.Name = "Toggle Point Deletion Test"
> Set cht = chtobj.Chart
> cht.HasLegend = False
> cht.ChartType = xlXYScatter
> cht.DisplayBlanksAs = xlInterpolated
> Set s = cht.SeriesCollection.NewSeries
> s.XValues = "=" & wb.Name & "!XRng"
> s.Values = "=" & wb.Name & "!YPlotRng"
> s.MarkerStyle = xlMarkerStyleNone
> s.Border.Color = vbBlue
> Set s = cht.SeriesCollection.NewSeries
> s.XValues = "=" & wb.Name & "!XRng"
> s.Values = "=" & wb.Name & "!YRng"
> s.MarkerStyle = xlMarkerStyleSquare
> s.MarkerSize = 6
> s.MarkerBackgroundColorIndex = 3
> s.MarkerForegroundColorIndex = 3
> Call SetMyChart
> c.Select
> txt = "Operation Instructions:" & vbCr & vbCr & _
> "1) Hold down the <Alt> key and click the desired points to toggle " & _
> "their exclusion/inclusion from the line plot." & vbCr & _
> "2) Don't click the points too quickly in succession or this will " & _
> "activate the Format Object dialog." & vbCr & _
> "3) If you accidently activate the Format Object dialog then close " & _
> "it and activate the worksheet before continuing." & vbCr & _
> "4) You need to reset the chart to the Class1 class (i.e. run SetMyChart) "
> & _
> "each time you open the workbook. Suggested is that you use the " & _
> "Workbook_Open event to accomplish this."
> MsgBox txt, vbInformation, "Chart Set-up"
> End Sub
>
>
> "[email protected]" wrote:
>
> > I'd be very abliged. Thanks for your input.
> >
> >