• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Locate data records from scatter plot

I have an Excel scatter plot on which I have visually identified specific points for which I would like to locate within the set of records that was used to build the plot.

I can do this by:
1) hovering over the point to determine the exact x,y values for each point of interest
2) manually scanning the data table to find the x,y pair that matches these values

However, this cumbersome if there are more than a few points of interest or multiple plots to investigate.

Suggestions for how to accomplish this task in a more automated/interactive fashion are appreciated. Thank you in advance.
  • 5
  • 5
1 Solution
Well when you talk about 'automated/interactive' I say VBA. so if this is the route you prefer to go I first suggest you to post your sample workbook so that the solution would be catered to your specific case, then I would suggest I put in your sheet a textbox and a button called locate and you fill in the text box with a value and it will locate it in the graph ?

Will need your comments as to how you want that and if this is workable for you.
dougf1rAuthor Commented:
Thanks for the reply gowflow.

Here is an example workbook. The chart I would like to identify points from is indicated with a large red arrow pointing down. I also included a button called locate.

The idea to enter a value in a text box and use the locate button is a good one. However, do you think there is be a way to click on a point in the chart and have the values associated with the point become highlighted in the table? This way, if there were several points to identify, it would avoid having to manually enter the values in the text box each time.

Thanks for your help!
ok I like challenges and the ones you put are really nice as total new area for me.

Here is what I did.
The button that you created 'Locate' has been called Reset Highlight and beside it there is a yellow label that will register a count of highlights.

Each time you click on a point in the graph (Chart 4) that you pointed out (I only applied this routine to this chart only) then the selected point will be highlighted in yellow in the table and the counter will increment the highlighted items.

The idea is to be able to rest highlighting which when you press on the button it will remove all yellow highlighting from the table and reset  the counter.

Try it and let me know.

As far as code this is the Main routine but for sure there are more smaller routines that you can find in the attached workbook.

Public WithEvents Chrts As Chart

Private Sub Chrts_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim WS As Worksheet
Dim Ser As Series
Dim Pt As Point
Dim PtRng As Range
Dim RngPoint As String
Dim Frmla As Variant

'---> If all selected then Exit
If Arg2 = -1 Then Exit Sub

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set WS = ActiveSheet

If InStr(1, Chrts.Name, "Chart 4") <> 0 Then
    'Chrts.GetChartElement x, y, IDNum, a, b
    If ElementID = xlSeries Then
        Set Ser = Chrts.SeriesCollection(Arg1)
        Set Pt = Chrts.SeriesCollection(Arg1).Points(Arg2)
        Frmla = Split(Ser.Formula, ",")
        RngPoint = Frmla(1)
        For Each PtRng In WS.Range(RngPoint)
            WS.Range(PtRng.Offset(Arg2 - 1, 0).Address).Interior.ColorIndex = 6
            WS.Range(PtRng.Offset(Arg2 - 1, 1).Address).Interior.ColorIndex = 6
            HighlightCount.Caption = Val(HighlightCount.Caption) + 1
            Exit For
        Next PtRng
    End If
End If

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

Open in new window

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

dougf1rAuthor Commented:
This is exactly the functionality I was looking for gowlfow!

The only thing is that I get an error (attached) when I open the file. If I close debugger, I can get the highlighter to work after I click around a few times on the points and the reset button.

Any ideas on why I get the error?
You are very much correct. I am sorry for this it was maybe a last minute change. I had to re-think the opening of the file so that the macro launches automatically. The problem here is that it is not easy to capture Chart events this is why we need to create a variable for that and the problem is the initialization of the variable once the file opened.

It has been (I hope fixed in this version)

Let me know.
dougf1rAuthor Commented:
Works perfectly. Thanks again!
dougf1rAuthor Commented:
Can you please indicate where in the code I would need to make modifications for this to apply to a different chart that may exist in the same workbook or another workbook?
Well it is not automatic ! Code has to be applied depending on the chart. But basically this is the main instruction that decide to handle chart 4
If InStr(1, Chrts.Name, "Chart 4") <> 0 Then

you may change "Chart 4" with a string of an other chart and just try (I say well TRY) to see if this only change could give you the expected result. But then again you have the button and the label that record highlighting that need duplication and you have to check this instruction in all of the following Sub

Sub SetChart()
Sub ClearHighlight()
Private Sub Chrts_Select

Again it is not guarantee that it will work correctly by changing these as then you would have more then 1 chart being monitored and do not know the behavior at this point.

dougf1rAuthor Commented:
Ok. Thanks for these tips on modifying the code for other charts.
your welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now