Re: XL2002: XY Chart and Image Position...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Trevor,

My address was in the "Reply to" field of my previous post, though not this
message. Here it is again, without the obvious punctuation -
pmbthornton gmail com

If by "shapes whose thresholds overlap" means overlapping shapes, then the
macro I posted would return the bottom-most of any shape that's over the
same screen position as the XY chart point. Could be adapted to return all
shapes under the point.

Not sure what this means -
" the code seems to use the 'frame' of the shape, not the lines of the
shape."

If you mean when the shape's border is close to the XY-point the macro may
be return incorrectly - indeed as posted that may well be the case, could be
a few pixels out. The reason for that is the macro assumes top-left pixel in
the chart to be same as the chart's object position (for use with
GetChartElement), but it isn't quite due to the chartobject's border.

After posting I tweaked a little and got it almost spot on, also made it a
little faster by only looping pixels in the PlotArea. FWIW I think it's a
really nice little macro that serves no useful purpose, at least that I can
think of ! So I would be interested to see your intentions with it.

Regards,
Peter T



"Trevor Williams" <TrevorWilliams@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:8EFB5456-3288-4514-9B1D-C4A1940B1A21@xxxxxxxxxxxxxxxx
Hi Peter - hope you had a good weekend.

The code you've sent works well - thank you...

However, the image that I'm using uses shapes whose thresholds overlap,
and
the code seems to use the 'frame' of the shape, not the lines of the
shape.
(hope that's clear.)

I couldn't see your disguised e-mail in your message - perhaps it's to
well
disquised for me?! - I'd like to send it over to you if the offer's still
there.

Regards

Trevor


"Peter T" wrote:

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


"Trevor Williams" <TrevorWilliams@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:CF1E1C2C-013E-43CA-B7B3-A6564D2B098F@xxxxxxxxxxxxxxxx
ha ha ha, thanks for the response Peter, and no, speed isn't
important...
(ish)

Yes, shapes exist on the worksheet, and yes the chart order is 'on
top'

Regarding visibility, it will probably be the opposite - the chart
will be
visible and the shapes won't.

Once the name of the shape which the plot lies within is returned, I
will
use a lookup to show a specific screen.

I'll be happy to mail the image to you on a personal e-mail if you
think
it
will help.

Thanks again

Trevor

"Peter T" wrote:

Are you saying you have shapes on the worksheet, not on the chart,
and
chart's Order is "on top".

Presumably ChartArea's & Plot's Fill are invisible so you can see
through
to
shapes and pictures, though that's not directly relevant.

Is speed important (say no!)

Regards,
Peter T


"Trevor Williams" <TrevorWilliams@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:885DE84D-F055-40B4-B3F5-39D022542323@xxxxxxxxxxxxxxxx
Hi All,

I have a simple XY graph, plotting 1 point only. This graph is in
front
of
an image made up of several shapes.

What I would like to do is return the name of the shape that the
plot
sits
over.

Sounds simple, but where to start?

Any help appreciated.

Trevor







.

Relevant Pages

  • Re: Creating charts in non-English versions of Excel
    ... Peter. ... When first attempting to apply a built in custom chart type ... Dim sTypeName As String ... On Error GoTo errH ...
    (microsoft.public.excel.programming)
  • Re: XL2002: XY Chart and Image Position...
    ... same screen position as the XY chart point. ... little faster by only looping pixels in the PlotArea. ... "Peter T" wrote: ... Dim x As Long, y As Long, k As Long ...
    (microsoft.public.excel.programming)
  • Re: XL2002: XY Chart and Image Position...
    ... trapezoids are smaller than the rectangular shapes that contain them, ... Much better to dispense with the chart altogether, some trig and algorithms, ... a few pixels out. ... Dim x As Long, y As Long, k As Long ...
    (microsoft.public.excel.programming)
  • Re: Excel 2007 -- Automation Error
    ... "Peter T" wrote: ... ChartSkel.xls can be looked at as my chart template. ... that I process the macro will create two tabs within the workbook. ... Dim p1 As Long, p2 As Long ...
    (microsoft.public.excel.programming)
  • Re: Update Chart by column
    ... serHighlit = serHighlit + x ... If you only want one series on the chart at any one time, ... > Thanks Peter for your reply! ... >> Dim sr As Series ...
    (microsoft.public.excel.programming)