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

YAY!
Hero status you have!

I am going to be a bit busy for next few days but I will be playing around with it, aim to send out the file to sales people, but it will be in a more dynamic manner,
which means I am more than likely going be coming back to you for help!

Honestly so grateful for your help!

 

[Rachaelgrocott]

Hi all

I would like data labels when I hover over data points, or clock on, or anything!

Any help would be greatly appreciated.

File is attached.

MANY thanks.

Rachael

https://www.dropbox.com/s/041mwlqh1hxmv5y/UVD Burnup Limpid Markets22.xlsx

 

[Andrew Poulsom]

Welcome to MrExcel Rachael.

What have you tried? There is no VBA code in your workbook (unsurprisingly as it's an xlsx).

 

[Rachaelgrocott]

That's weird. I have, in the chart VBA:

' ** 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 Ch.SeriesCollection(a).Points(b)
.HasDataLabel = True
Txt = Worksheets("Data").Range("B1:B5").Cells(b, 1).Value
Txt = Txt & " - " & Worksheets("Data").Range("B1:B80").Cells(b, 1).Value
Txt = Txt & " [" & Worksheets("Data").Range("B1:B80").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

 

[Andrew Poulsom]

If you save as xlsx your VBA code will be lost.

You don't need a Class module for a Chart on a Chart sheet. You can use code in the Sheet's module.

 

[Rachaelgrocott]

Ahh, I didn't know! (told you I'm new!)

So will your code at the start of this thread work if i use it in the sheet's module?

Thank you - sorry for my ignorance.

 

[Rachaelgrocott]

https://www.dropbox.com/s/34k2eyuigx9dua0/UVD Burnup Limpid Markets22.xlsm

 

[Andrew Poulsom]

Does the code in Post #6 work for you?

 

[Rachaelgrocott]

Ah, YES! Excellent.

Thank you. Is there a way for the boxes to disappear again, or am I just being cheeky now?!

Thanks again,

Rach

 

[Andrew Poulsom]

Don't they disappear when you release the mouse button?