Actually which object is on top and visibility is not relevant. You've got
me curious as to what you're doing with your lookup and how that relates to
a series point whose position is not easy to accurately control. If not
simple to explain I'd be pleased to look at what you're up to (my address is
disguised in the "reply to").
This macro is simplified in a number of respects but hopefully will work.
See comments about "ppp", for distribution to unknown users would require
API's. Uses brute force rather than calculating the point's position, hence
why I asked about speed. Speed could be improved by starting from top left
of the plot, though speed is better than I expected as should be fine to
"skip" pixels.
For testing I suggest make chartarea & Plot fill's invisible with the chart
"on top" so you can see what shape the point is over (you did say a single
series with single a point - right).
Sub SeriesPointOverShape()
Dim b As Boolean
Dim x As Long, y As Long, k As Long
Dim xx As Single, yy As Single
Dim elem As Long, arg1 As Long, arg2 As Long
Dim shp As Shape
Dim cht As Chart
Dim ppp As Single
' normally should get points per pixel with API's but
' but to simplify assume typical ppp for %90+ users at 0.75
ppp = 0.75
' oop pixels from top-leftt of chart until
' the series-1 is found with GetChartElement
Set cht = ActiveSheet.ChartObjects(1).Chart ' CHANGE to suit
xx = CLng(cht.Parent.Width / ppp) ' width in pixels
yy = CLng(cht.Parent.Height / ppp)
For k = 10 To 1 Step -2
'start by looping every 10th pixel to save time
For y = 1 To yy Step k
For x = 1 To xx Step k
Call cht.GetChartElement(x, y, elem, arg1, arg2)
If elem = xlSeries Then
'found the one & only series with single point, exit the loops
'(if need a particular series & point - check arg1 & arg2)
b = True
Exit For
End If
Next
If b Then Exit For
Next
If b Then Exit For
Next
If b Then
'convert chart pixel co-ord to worksheet point co-ord
xx = cht.Parent.Left + x * ppp
yy = cht.Parent.Top + y * ppp
b = False
'loop if/until our co-ord intersects a shape
For Each shp In ActiveSheet.Shapes
If shp.Name <> cht.Parent.Name Then
With shp
If xx >= .Left Then
If xx <= .Left + .Width Then
If yy >= .Top Then
If yy <= .Top + .Height Then
b = True ' got it
Exit For
End If
End If
End If
End If
End With
End If
Next
If b Then MsgBox shp.Name
End If
End Sub
Regards,
Peter T