(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]

I assume you are referring to this post:

Identifying data points on a chart

I think the MouseMove event fires too often to be usable - it causes a lot of screen flicker. That's why I used MoseDown and MouseUp.

If your Chart is embedded on a worksheet you need a Class Module to enable events (as in my post). But if your Chart is on a Chart sheet the events are already available in the Module for the Chart Sheet.

What problems are you having implementing the code?

 

[semiclassical]

Thanks for replying, and yes that was the post I was referring to. I have used excel for a while, however do not have any experience using macros, scripts, vba etc. I would like the functionality I mentioned with my Chart on a Chart Sheet. You mention that the events are available in the Module for the Chart Sheet.

How do I access the Module and the events? What changes would I make?

Perhaps this is too much to answer and I would understand

 

[Andrew Poulsom]

Activate the Chart sheet and press Alt+F11 to enter the Visual Basic Editor. In the Project Window double click the Chart Sheet. You should see a blanc Code Window on the right. In the left hand dropdown choose Chart. You will get this:

Private Sub Chart_Activate()

End Sub

which you can delete.

In the right hand dropdown choose MouseDown. You will get this:

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

End Sub

Paste my Mouse_Down code in there (excluding the Sub and End Sub lines). Repeat for MouseUp. Press Alt+F11 to return to your Chart and try it out.

Let's see how we go from there.

 

[semiclassical]

... Okay. I have posted the code in the appropriate subroutines as you mentioned. I did have to delete the lines with "Ch.GetChartElement x, y, IDNum, a, b" along with excluding the Sub and End Sub lines.

What should happen next?

 

[Andrew Poulsom]

Sorry that's my fault. Instead of deleting those lines Ch should be ActiveChart. And you need to declare some variables at the top. Here is the whole code:

Dim IDNum As Long
Dim 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

 

[semiclassical]

... works very nicely now.

How can I point to a text column in the data worksheet in place of "other text here"?

 

[Andrew Poulsom]

What is the range of data that you are plotting, and in which column are the labels?

See if you can adapt the code below:

Txt = Txt & " - " & Worksheets("Sheet1").Range("A2:A5").Cells(b, 1).Value

That assumes the labels are in the range A1:A5 on Sheet1 - b is the point number, returned by GetChartElement.

 

[semiclassical]

... you can't see me, but I am jumping with glee. Thanks for all the help. Two last issues:

1. Any thoughts on why there is the screen flicker?
2. When clicking on the data point the label is displayed and the data set is also selected. Is it possible to prevent the data set from being selected?

 

[Andrew Poulsom]

1. Application.ScreenUpdating = False at the beginning of the code and Application.ScreenUpdating = True at the end might help with the flicker.

2. What do you mean "the data set is also selected"?