(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

 

[Rachaelgrocott]

Hi, no they stay there.

 

[Andrew Poulsom]

Isn't this working for you?

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

 

[Rachaelgrocott]

Hi,

No, they all stay there..?

Thanks

 

[Andrew Poulsom]

Please post all your Chart code and I'll have a look.

 

[Rachaelgrocott]

Dim IDNum As LongDim a As Long
Dim b As Long


Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Txt = ""
    ActiveChart.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 Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    ActiveChart.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

 

[Rachaelgrocott]

Hi Thank you. I also can't suss out how to get the labels on with this code:


Txt = Txt & " - " & Worksheets("Sheet1").Range("A2:A5").Cells(b, 1).Value</pre>
The labels are in sheet3 A2:A13.

 

[Andrew Poulsom]

For some reason the Chart_MouseUp event isn't firing in Excel 2010. It works in Excel 2003 and Excel 2013. Maybe you can adapt the example here:

Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison

 

[Rachaelgrocott]

That's a shame isn't it. I'll try that then, thank you. I'm not too good at VBA though!

Thank you very much for all of your help

 

[hrjohnson]

Hello,
Thank you for this code it works great for me! I have a question about the amount of text that I can display in the pop-up window.
Most of the pop ups work, but some of the text I am displaying has multiple lines of text (like 10+) and when I try to click on those I get the error:
"Run-time error '-2147467259 (80004005)':
Method 'Text' of object 'DataLabel' failed"

This is my code:

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Dim n As Integer, row_log As Integer
    
    Txt = ""
    ActiveChart.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            For n = 4 To 90
                If .Parent.Name = Worksheets("Log_comp").Cells(n, 1) Then
                    row_log = n
                End If
            Next n
                    
            'Txt = "Series " & .Parent.Name
            'Txt = Txt & " point " & b
            'Txt = Txt & " (" & .DataLabel.Text & ")"
            'Txt = Txt & " - " & "TEST"
            Txt = Txt & Worksheets("Log_comp").Cells(row_log, 2)
            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

I am grabbing this text from a cell on another worksheet. Here is an example of what I'm printing:

[TABLE="width: 923"]
<tbody>[TR]
[TD]Check 37
Check 37 exceeds drawdown limit at 1:00 compared to yesterday at 11:00 by 0.0'
Check 37 exceeds drawdown limit at 1:00 compared to yesterday at 10:00 by 0.2'
Check 37 exceeds drawdown limit at 2:00 compared to yesterday at 11:00 by 0.2'
Check 37 exceeds drawdown limit at 2:00 compared to yesterday at 10:00 by 0.3'
Check 37 exceeds drawdown limit at 3:00 compared to yesterday at 15:00 by 0.0'
Check 37 exceeds drawdown limit at 3:00 compared to yesterday at 14:00 by 0.0'
Check 37 exceeds drawdown limit at 3:00 compared to yesterday at 11:00 by 0.2'
Check 37 exceeds drawdown limit at 3:00 compared to yesterday at 10:00 by 0.4'
Check 37 exceeds drawdown limit at 4:00 compared to yesterday at 15:00 by 0.1'
Check 37 exceeds drawdown limit at 4:00 compared to yesterday at 14:00 by 0.1'
Check 37 exceeds drawdown limit at 4:00 compared to yesterday at 11:00 by 0.3'
Check 37 exceeds drawdown limit at 4:00 compared to yesterday at 10:00 by 0.4'
Check 37 exceeds drawdown limit at 5:00 compared to yesterday at 15:00 by 0.0'
Check 37 exceeds drawdown limit at 5:00 compared to yesterday at 14:00 by 0.1'
Check 37 exceeds drawdown limit at 5:00 compared to yesterday at 11:00 by 0.2'
Check 37 exceeds drawdown limit at 5:00 compared to yesterday at 10:00 by 0.4'

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

Is there a limit on how much text can be displayed in the pop up box and is there a workaround?

Thank you in advance.

 

[Andrew Poulsom]

It appears that the limit is 255 characters.