(x,y) scatter plot label vba implementation question

S

semiclassical

New Member
Joined
Sep 2, 2004
Messages
7
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
 
Sort by date Sort by votes
These are the choices:

<TABLE style="WIDTH: 125pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=166 x:str><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 125pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 width=166>xlLabelPositionAbove
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionBestFit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionCustom</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionInsideEnd</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionMixed</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionRight</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionBelow</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionCenter</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionInsideBase</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionLeft</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>xlLabelPositionOutsideEnd

</TD></TR></TBODY></TABLE>
but some apply only to a column chart.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi

I am new to the world of VBA and am keen to learn how to use this hover technique, and having played around for about half a day, I have come to the conclusion that I am going to have to ask for help.

What I have done so far is:

Make my chart,
Alt-F11
Insert - Class module
Choose chart - mouse down
paste this code:

-------------------------------------------
' ** Class module named Class1 **
Code: 
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 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 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
-------------------------------------------

double click on the left hand side on the chart file paste this code:
-------------------------------------------

** Sheet module code to connect to the Class module **
Code: 
Dim MyChart As New Class1
Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub
-------------------------------------------
then go back to excel, open up a different tab, then open up the chart tab
and nothing happens,

Can you please help me!
 
Last edited by a moderator:
Upvote 0
Also forgot to say, when I do it, it always says:
Compile Error: Ambiguous Name Detected: Chart_Mousedown

Thanks again if someone can help!?!?!?
 
Upvote 0
This step:

Choose chart - mouse down

should not be present.
 
Upvote 0
Sothis is my code now


Row 1 is Title
Column A is Names that I want
Column B and C are the values


I really dont understand what I am doing wrong???

At the moment, no error emssage is coming up, all that is happening, is apparently nothing :(

Code: 
' ** Class module named Class1 **
Public WithEvents Ch As Chart
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 = ""
Chart.GetChartElement x, y, IDNum, a, b
If IDNum = xlSeries Then
With ActiveChart.SeriesCollection(a).Points(b)
.HasDataLabel = True
            Txt = Worksheets("Data").Range("A1:B80").Cells(b, 1).Value
            Txt = Txt & " - " & Worksheets("Data").Range("A1:B80").Cells(b, 1).Value
            Txt = Txt & " [" & Worksheets("Data").Range("A1: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)
Chart.GetChartElement x, y, IDNum, a, b
If IDNum = xlSeries Then
With ActiveChart.SeriesCollection(a).Points(b)
.HasDataLabel = False
End With
End If
End Sub
 
Last edited:
Upvote 0
You should be referring to ch not Chart or activechart
Code: 
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
End Sub
 
Upvote 0
Sorry for being so dim here, but still not working
I copied and pasted your code, the values in the cells I changed from a1:C80 because A1:A5 didnt work either.

it just says Series "blah blah" Point "12", then below that is the co-ordinates
is there something you can see that I am doing wrong still?


my code for the class module is:

Code: 
' ** Sheet module code to connect to the Class module **
Dim MyChart As New Class1
Private Sub Worksheet_Activate()
    Set MyChart.Chart = ActiveSheet.ChartObjects(1).Chart
End Sub
Private Sub Chart_BeforeRightClick(Cancel As Boolean)
    Cancel = True
End Sub
Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Cancel = True
End Sub

I added the things at the end to stop double and right clicking (from previous posts).
 
Upvote 0
Remove the bits at the end - they won't do anything (unless this is a chart sheet in which case none of this will work!). Your code shouldn't even compile let alone run.
Code: 
' ** Sheet module code to connect to the Class module ** 
Dim MyChart As Class1
 Private Sub Worksheet_Activate()
    Set MyChart = New Class1
     Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
 End Sub
 
Upvote 0
Rory, I don't know whats happening and why it wont work for me.

So, heres the code I use as a class module


Code: 
 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("A2:A98").Cells(b, 1).Value
            Txt = Txt & " - " & Worksheets("Sheet1").Range("A2:A98").Cells(b, 1).Value
            Txt = Txt & " [" & Worksheets("Sheet1").Range("A2: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

Are there any errors in there? The bits I am unsure of is this bit:
Code: 
.HasDataLabel = True
            Txt = Worksheets("Sheet1").Range("A2:A98").Cells(b, 1).Value
            Txt = Txt & " - " & Worksheets("Sheet1").Range("A2:A98").Cells(b, 1).Value
            Txt = Txt & " [" & Worksheets("Sheet1").Range("A2:A98").Cells(b, 1).Value
            Txt = Txt & "] "
            With .DataLabel

Then I pasted what you said to me into the my worksheet, and uing scrolldown made sure it was for 'Worksheet' and not general
but it highlights this line:
Code: 
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
as it comes up with error message when i try to activate the worksheet!


If you cannot see the error, is there any chance you could send me a file which is very basic, but shows how it is done, maybe I can work from that file backwards?

Thanks for your help so far, really appreciate it!

Tej
 
Upvote 0
Is that the entire code in the class module? If so, you are missing these lines:
Rich (BB code): 
Public WithEvents Ch As Chart
Dim IDNum As Long
Dim a As Long
Dim b As Long
 
Upvote 0
You must log in or register to reply here.

Similar threads

Atholl
  • Solved
Adaptive x axis spacing on change of y value
Replies
3
Views
515
JamesCanale
JamesCanale
M
  • Solved
VBA Chart Series title with multiple cells
Replies
4
Views
99
mikejvir
M
W
  • Question Question
Labelling single point on scatter graph with values from cells
Replies
2
Views
887
willow-mouse
W
T
  • Question Question
Can you change what pops up when you mouse-over a chart point?
Replies
2
Views
747
TomCon
T
B
  • Question Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
749
Jon Peltier
J

Forum statistics

Threads
1,223,975
Messages
6,175,746
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top