Find a data point on XY scatter

[anonymoose]

Me again - the problem with all the very useful help that everyone gives me here is that I'm back again with more ideas from the users who are loving the work!

So, thinking caps on . . .

I now have a XY scatter graph with 109 individual points (representing schools) and a macro that tells you the plot details when you hover over. It's ok if you want to know which school is down the bottom but if you don't know where the desired school is in the first place, it's a lot of trial and error before you find it.

What could I look to add that gives the user the option to have a single data point highlighted (on selection?) so they don't have to manually look for it?

Cheers

 

[Andrew Poulsom]

How would you want to select the data point?

 

[anonymoose]

Hi Andrew

That's what I'm not sure of - I don't want the users to have to type anything (they may not even type it correctly!) so I was thinking along the lines of a drop-down list, choose the school, change the point colour?

 

[Andrew Poulsom]

Can you post a small but representative sample of your data and the the chart range references?

 

[anonymoose]

The chart range references for KS2 English is

=Chart_Data!$C$2:$C$110,Chart_Data!$G$2:$G$110

Excel Workbook
	A	B	C	D	E	F	G	H
1	School	EnMa	En	Rea	Wri	Ma	2lvlEn	2lvlMa
2	Abberley Parochial VC Primary School	90	100	100	90	90	100	88.9
3	Astley CofE Primary School	64	91	91	73	73	81.8	72.7
4	Bayton CofE Primary School	69	100	100	100	69	100	69.2
5	Beaconside Primary and Nursery School	67	89	93	85	74	81.5	70.4
6	Belbroughton CofE Primary School and Nursery	100	100	100	90	100	90.5	100
Chart_Data

So, the English graph takes column C as the X and column G as the Y

The data is populated on this sheet by some IF / vlookups that are driven by changing the year

 

[anonymoose]

Reminder:

Your macro is running on this chart!

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'   macro that gets chart to show user details of a point on the chart when the mouse is moved over it
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX, myY As Variant, myName As Variant
    With ActiveChart
        .GetChartElement x, y, ElementID, Arg1, Arg2
        If ElementID = xlSeries Then 'checks to see which part of the chart the mouse is over
            If Arg2 > 0 Then 'if the mouse is over a plotted point on the chart
                ' Extract x and y values from array
                myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2) & "%" 'X Value
                myY = " - " & WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2) & "%" 'Y Value
                End If
                myName = Worksheets("Chart_Data").Range("A" & Arg2 + 1).Value & " | " 'School Name
            Else: myX = "Move mouse over a point to see which school it is"
                myY = ""
            End If
        
    End With
    ActiveChart.Shapes("Text Box 2").Select
    Selection.Characters.Text = myName & myX & myY 'show the school name and percentages in the text box
    ActiveChart.Deselect
End Sub

 

[Andrew Poulsom]

Using your data I created an XY Scatter chart on the worksheet using columns C and G. I named the list of schools in A2:A6 Schools, added a ComboBox from the Control Toolbox and set its ListFillRange property to Schools. I then assigned it this code:

Private Sub ComboBox1_Change()
    With ActiveSheet.ChartObjects(1).Chart
        With .SeriesCollection(1)
            .MarkerBackgroundColorIndex = xlColorIndexAutomatic
            .MarkerForegroundColorIndex = xlColorIndexAutomatic
            .MarkerStyle = xlAutomatic
            .MarkerSize = 5
            With .Points(ComboBox1.ListIndex + 1)
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 36
                .MarkerSize = 10
            End With
        End With
    End With
End Sub

Selecting a school from the ComboBox causes the appropriate data marker to double in size and become red with a yellow border.

You will need to amend the code if your chart is on a Chart sheet.

 

[anonymoose]

You sir, are a star and a legend

I am using a chart sheet, so am I right in thinking that I have to use a Forms Combo Box?

So I've changed the first couple of lines to be;

Private Sub DropDown3_Change()
    With ActiveChart.ChartArea.Select

Am I on the right lines?

 

[Andrew Poulsom]

Make sure you dropdown is name DropDown3:

Private Sub DropDown3_Change()
    With ActiveChart
        With .SeriesCollection(1)
            .MarkerBackgroundColorIndex = xlColorIndexAutomatic
            .MarkerForegroundColorIndex = xlColorIndexAutomatic
            .MarkerStyle = xlAutomatic
            .MarkerSize = 5
            With .Points(ActiveSheet.DropDowns("DropDown3").ListIndex)
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 36
                .MarkerSize = 10
            End With
        End With
    End With
End Sub

You will need to remove the Private keyword to assign the macro.

 

[anonymoose]

I've tried it and I'm getting a run-time error '1004':

Unable to get the DropDowns property of the Chart class

The debugger highlights this line

With .Points(ActiveSheet.DropDowns("DropDown3").ListIndex)

Do I have to assign a name to the dropdown - I understand you can change it with the Control Toolbox properties but not with Forms dropdown?