Identifying data points on a chart

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,382
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

Code:
' ** 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
 
Upvote 0
Thanks, Andrew, I'll give it a whirl.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,710
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top