MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Scatter Chart

  Asked By: Binge    Date: Feb 23    Category: MS Office    Views: 1387

I have a spreadsheet which contains three columns of
data - Size, Age and Name. I am trying to create a scatter chart
where I when I place my insertion point over the size and see the
corresponding name.

For example, using the information below, I can create a scatter chart
using the Age and Size. When I place my mouse over point 8,4, I want
to see juanita.

Age Size Name
21 2 rhonda
6 1 elisa
8 4 juanita
10 9 charlotte
24 14 ethnie
38 12 missy

Can anyone over any suggestions? Is it possible?



2 Answers Found

Answer #1    Answered By: Abel Fischer     Answered On: Feb 23

If I didn't get wrong,i think this helps:

Put this in worksheet's code.I assume the data is in Range("A1:C6")

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
If(ActiveCell.Row<7) and (ActiveCell.Column=1 or ActiveCell.Column=2)
Range("C" & ActiveCell.Row).Select
Msgbox Selection.Value
'Delete Msgbox line and put the Code with using Selection
End If
End Sub

Answer #2    Answered By: Julia Silva     Answered On: Feb 23

I sent an example  file to bigrho1, but for the rest of the group, if
you are interested in how to make custom chart  tips, read on...

Work through the examples here:


Using the data from the original email below, add a scatter chart to
your sheet. *Select* your chart and draw a rectangle *inside* it.
This will later be used to display the custom chart tips on the
chart, so make it sized for 2 lines of text and whatever color you
want. Disable the normal excel "Chart Tips" under Tools-Options-
Chart-Chart Tips.

Then, use the modified code below  for your MouseMove events.

A drawback is that the chart has to be selected for this to work.

Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift
As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Did we hover over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)
'Get the names
f = .SeriesCollection(Arg1).Formula
i2 = InStr(1, f, ",")
f = Mid(f, 9, i2 - 9)
myN = Range(f).Offset(Arg2, 1)
'move label to cursor location
.Shapes(1).Left = x * 0.75 + 10
.Shapes(1).Top = y * 0.75
'update the label caption
.Shapes(1).DrawingObject.Caption = myN & vbLf & myX
& ", " & myY
End If
End If
End With

End Sub

Didn't find what you were looking for? Find more on Scatter Chart Or get search suggestion and latest updates.