(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

 

[Andrew Poulsom]

You could change the Position from xlLabelPositionAbove to xlLabelPositionRight.

 

[phil152003]

I thought that had solved it... but now I have the same problem with the points that lie on the x-axis (there's quite a lot of points). Good suggestion though. Anything else you can think of?

 

[Andrew Poulsom]

You could test for the position of the point and change the position of the data label accordingly.

 

[phil152003]

That sounds like a good idea. If you'd be able to help me that would be greatly appriciated. I've changed the code to how I thought it would work, but it doesn't make any difference (the label still appears above the data point in both x<0.2 and x>=0.2 cases). Any ideas what I've done wrong? (As I've already mentioned I'm very new to VBA!)

Private Declare Function LockWindowUpdate Lib "user32" (ByVal hWnd As Long) As Long
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
    Debug.Print "mosuedown"
   LockWindowUpdate Application.hWnd
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries And x < 0.2 Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 4).Value
            Txt = Txt & " - " & Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 3).Value
            Txt = Txt & " [" & Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 2).Value
            Txt = Txt & "] "
            With .DataLabel
                 .Position = xlLabelPositionRight
                 .Font.Size = 20
                 .Border.Weight = xlHairline
                 .Border.LineStyle = xlAutomatic
                 .Interior.ColorIndex = 19
                 .Text = Txt
            End With
        End With
    End If
        If IDNum = xlSeries And x >= 0.2 Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 4).Value
            Txt = Txt & " - " & Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 3).Value
            Txt = Txt & " [" & Worksheets("Chart Calculation Summary").Range("B6:CC673").Cells(b, 5 * a - 2).Value
            Txt = Txt & "] "
            With .DataLabel
                 .Position = xlLabelPositionAbove
                 .Font.Size = 20
                 .Border.Weight = xlHairline
                 .Border.LineStyle = xlAutomatic
                 .Interior.ColorIndex = 19
                 .Text = Txt
            End With
        End With
    End If
LockWindowUpdate 0
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
Private Sub Ch_BeforeRightClick(Cancel As Boolean)
    Cancel = True
End Sub
Private Sub Ch_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Cancel = True
End Sub

 

[Andrew Poulsom]

The x and y coordinates are integers. Try something like this:

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
                If x < ActiveChart.Axes(xlValue).Left + 50 Then
                    .Position = xlLabelPositionRight
                Else
                    .Position = xlLabelPositionAbove
                End If
                .Font.Size = 8
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub

 

[phil152003]

That works really well thanks. Can you explain how it works? And where the "50" value comes from? I'd quite like to implement the label to appear below the data point when the point is near the top of the chart now I know this is possible. I'd like to modify your code but I don't understand how it works!

 

[Andrew Poulsom]

The 50 is just a guess really, to allow for the width of the label. What I did was add this to the code:

Debug.Print "x" " & x & " y " & y

Then I clicked on some points near the axes and looked at the results in the Immediate window. Once you know the maximum and minimum for each you can adapt the position accordingly.

 

[phil152003]

More out of curiosity I'm just trying to get my head around this now. When I evaluate the expression

ActiveChart.Axes(xlValue).Left

It returns then number 83. Now I would assume that this is the x coordinate in the chart of the y axis. Would that be correct? The thing that makes me think that I am wrong here, is that when I actually click on the y axis (using your debug code, viewing in the immediate window) it shows the y axis to have x coordinate ~57.

Secondly, if I was to use this code again a couple of times within the "if" function, so that the label appears to the left of the data point if it is within a certain disance of the right hand side of the chart (what would be the secondary y axis if it was enabled), and below the datapoint for points close to the top of the chart, how would I modify the code? Would

ActiveChart.Axes(xlValue).Right

ActiveChart.Axes(xlValue).Top

be the correct numbers to use?

EDIT: After a little experiement, it appears Excel doesn't like my guesses at what the code would be. If you could point me in the right direction that would be great.

Thanks.

 

[phil152003]

Okay, after playing around a little more, I now I have the left and right sides sorted. I never thought about it until just now, but I realised I could just add a large value to the code you gave, rather than minus a smaller values of the x coordinate on the secondary y axis. All that is left now is to work out how to get the y coordinate of the x axis, and I'm not sure how to do this. My modified code so far:

If x < ActiveChart.Axes(xlValue).Left + 35 Then
                    .Position = xlLabelPositionRight
                    ElseIf x > ActiveChart.Axes(xlValue).Left + 450 Then
                        .Position = xlLabelPositionLeft
                    Else: .Position = xlLabelPositionAbove
                End If

 

[phil152003]

Apologies for the triple post. But I'm wondering if there is a xlLabelPosition the makes the label appear what I would call AboveRight, or North-East if you were looking at a compass. That would be really useful for the data points that appear in the bottom left of the chart (i.e. whether the label appears above or to the right, it coincides with axis labels (either from x or y axis) and, as I mentioned earlier, muddles up the text).