Identifying data points on a chart

[pcc]

I need to plot a chart as a scatter chart. The chart is busy, and I don't want to display either data values or labels on it, since they will clutter it up too much. However, if possible, I would like to be able to hover the pointer over any point, and show additional info. for the point. eg by default, if I hover over a point, the yellow box that appears might say "Series 2 point 8 (8,50)" I would like it to say say "Series 2 point 8 (8,50) - other text here"
Anyone have any suggestions on how this might be achieved??

Thanks

 

[Andrew Poulsom]

I don't think you can access the yellow box, but this code will show a DataLabel if a point is clicked. Releasing the mouse button will remove the DataLabel. It assumes the Chart is on a worksheet, hence the need for a Class module.

' ** Class module named Class1 **

Public WithEvents Ch As Chart

Dim IDNum As Long
Dim a As Long
Dim b As Long

Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " point " & b
            Txt = Txt & " (" & .DataLabel.Text & ")"
            Txt = Txt & " - " & "other text here"
            With .DataLabel
                .Text = Txt
                .Position = xlLabelPositionAbove
                .Font.Size = 8
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub

Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

' ** Sheet module code to connect to the Class module **

Dim MyChart As New Class1

Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub

 

[pcc]

Thanks, Andrew, I'll give it a whirl.

 

[Eld0]

This is just what I needed. It works like a charm.

But can I do it the other way around. I mean, can I retrieve the x and y value of all points on the graph?
I want to do this so I can let the user select an area on the graph and I'll look which points are in the selected area.

Anyone?

 

[Eld0]

*bump*

Nobody that knows how to help meh?

 

[Eld0]

*bump* Anyone?

 

[Jon Peltier]

You need to use chart events.

http://www.computorcompanion.com/LPMArticle.asp?ID=221

 

[Hermi]

Hi,

I seem to be having trouble with this code, I had it working to the point it was displaying what I wanted it to display but I've now noticed that the description is not linking up to the right data points. Can you please tell me what I may be doing wrong.

Thanks,

Hermi

 

[rsauceda]

I used the code above to change the labels that appear when I click on a point on my graph. However, the text that shows up does not match with the specific x-y coordinates on the graph. Any ideas how I might fix this?

 

[largeselection]

I know this post is old, but it has exactly what I have been searching for. However, for some reason, the Ch_MouseUp doesn't work.

So when I click on a point it adds the data label nicely, but if I release the label just remains and I have to go in and delete it manually.

Any idea how to fix it?