removing data points chart

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Removing data points from a chart using vba | Excel forum

Removing data points from a chart using vba | Excel forum

resolvedResolved · Low Priority · Version 2003

Sarah has attended:
Excel VBA Intro Intermediate course
Access VBA course

Removing data points from a chart using vba

I am working on a template that graphs and compares data from multiple sources, I need to be able to use the graphs to see patterns in the data and also remove spikes or outliers from any one of 27 columns of data that create the separate charts.
So far I've been doing this manually but I've found some code that enables the user to select data points on a chart and parse the series name and data point to a message box to alert the user of the point selected.

The part I'm stuck with is getting a cell address from the series name and data point so I can write a section of code to clear the contents of the cell, and thereby remove the data point from the chart.

Here's the code so far. It works with the chart sheet that becomes active when the user selects it.

Private Sub EvtChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
'extracted and modified from http://www.computorcompanion.com/LPMArticle.asp?ID=221

Dim ElementID As Long
Dim a As Long
Dim b As Long
Dim msg As String
Dim myX As Date
Dim myY As Double
Dim Answer As Integer
Dim Counter As Integer
Dim QAFDest As Range
Dim NoRows As Integer

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, a, b

If ElementID = xlSeries Then
If b > 0 Then
' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(a).XValues, b)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(a).Values, b)


' Display message box with point information
msg = "You are about to remove the following point from data Series " & vbCrLf _
& """" & .SeriesCollection(a).Name & """" & vbCrLf _
& "Point " & b & vbCrLf _
& "Value = " & myY & vbCrLf _
& "Continue?"

If MsgBox(msg, vbOKCancel) = vbOK Then
'Sheet4.Cells(b, ?).ClearContents
End If
End If
End If
End With
End Sub

I've been hunting for a solution on the internet and baking my noodle for a almost a week now but I'm no closer to finding the answer. I think I must be missing something obvious. Any help would be gratefully received!

Thanks
Sarah

RE: removing data points from a chart using vba

Hi Sarah

To assist with your requirement will most likely involve some didicated time with a relevant trainer having access to your spreadsheet.

Naturally this will be consultative work and therefore billable.
If you wish to discuss this in more detail please do let us know.

Regards

Jacob

RE: removing data points from a chart using vba

It's ok thanks, I fixed the problem while I was waiting. After two weeks of banging my head against dead ends the answer came to me.

Thanks for getting back to me anyways,

Sarah

RE: removing data points from a chart using vba

Hi Sarah

Sorry for the delay in getting back to you. This is indeed a quite involved requirement. To be fair we will need to allocate some time to reviewing this and being able, if we can, to respond with a resolution.

This will mean some billable time as we are dedicating a specific person to help with your requirement.

If you wish to explore this further please let us know.

Kind regards

Jacob

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Server loaded in 0.07 secs.