(x,y) scatter plot label vba implementation question

[semiclassical]

Problem:

I have a scatter plot with 150 points closely grouped. Enabling the label option clutters the graph. A cleaner way would be to hoveri the mouse over a data point and pop-up a box displaying it's value (x,y) as well as a label contained in an adjacent column.

Details:

The default functionality is that when the mouse hovers over a particular point the yellow pop-up box appears with

• 
  Data set label
  Data point name "x"
  Data point (x,y)

and one can choose to show "label-name-point" or just "point" by accessing the Tools-Options_Charts menu item. What I would like instead is to hover the mouse over a particular point and get the yellow pop-up box with the following functionality

• 
  Data set label
  Data point name "label"
  Data point (x,y)

where the label is contained in an adjacent column in my data spreadsheet.

I read a post by Andrew Poulsom that stated it is probably not possible to customize the yellow pop-up baox and then offered some vba code to allow a mouse click to bring up a label. I was wondering how to implement this code in the spreadsheet. Also, does the graph have to be embedded in the worksheet "as object in", or can I use "As new sheet"?

Any help appreciated...

Aaron

 

[Tejkaran]

Rory!

THANK YOU! It works now! Really big help you've been(for some reason I am speaking like Yoda now, lol).

just one thing, It work now, and I click a cell and the name sticks to the graph, is there anyway to have it so that I click it, it comes up, but then click it and it goes away?

 

[RoryA]

welcome you are

understand your second question, I do not.

 

[Tejkaran]

I think your knowledge makes you more of a Yoda than me

so at the moment, I click on a point with cordinates(x,y), it comes up with the name e.g. 'London.'
This is a seperate box, (so I can still hover over it and the hover co-ordinate thing comes up), but what I would want ideally is that when you click it the name comes up, and when you are no longer looking at it, you can click it again and it will go away.

Or if possible, and I think this may be taking it a bit too far; but is it possible, that instead of having the co-ordinates and basic info in the hover box, the info in the hover box is replaced with the info I want i.e.'London'.

 

[RoryA]

using the code you have the datalabel should be removed when you release the mouse button

 

[Tejkaran]

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 Ch.SeriesCollection(a).Points(b)
            .HasDataLabel = True
            Txt = Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).Value
            Txt = Txt & " - " & Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).Value
            Txt = Txt & " [" & Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).Value
            Txt = Txt & "] "
            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 Ch.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

Would the error be in there?

I thought I had it all going right, doh, i get the feeling the next 6 months (as a start) are going to be hard for me, lol!

 

[RoryA]

You might try changing the last part to:

Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)   On Error Resume Next
    If IDNum = xlSeries Then Ch.SeriesCollection(a).Points(b).HasDataLabel = False
End Sub

 

[Tejkaran]

no, didn't do anything/still stays there when i release the mouse

 

[RoryA]

Which version of Excel are you using?

 

[Tejkaran]

Excel 2010

 

[RoryA]

It seems the mouseup event is unreliable when clicking a series or point so perhaps use the mousemove event to clear the markers

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 Ch.SeriesCollection(a).Points(b)
            .HasDataLabel = True
            Txt = Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).value
            Txt = Txt & " - " & Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).value
            Txt = Txt & " [" & Worksheets("Sheet1").Range("A3:A98").Cells(b, 1).value
            Txt = Txt & "] "
            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_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    ' clear old marker
    On Error Resume Next
    Ch.SeriesCollection(a).Points(b).HasDataLabel = False
End Sub