A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

XL2002: XY Chart and Image Position...



 
 
Thread Tools Display Modes
  #1  
Old March 28th 08, 09:59 AM posted to microsoft.public.excel.programming
Trevor Williams
external usenet poster
 
Posts: 181
Default XL2002: XY Chart and Image Position...

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
Ads
  #2  
Old March 28th 08, 02:47 PM posted to microsoft.public.excel.programming
Peter T
external usenet poster
 
Posts: 5,600
Default XL2002: XY Chart and Image Position...

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" > wrote in
message ...
> 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



  #3  
Old March 28th 08, 03:15 PM posted to microsoft.public.excel.programming
Trevor Williams
external usenet poster
 
Posts: 181
Default XL2002: XY Chart and Image Position...

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" > wrote in
> message ...
> > 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

>
>
>

  #4  
Old March 28th 08, 03:57 PM posted to microsoft.public.excel.programming
Peter T
external usenet poster
 
Posts: 5,600
Default XL2002: XY Chart and Image Position...

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" > wrote in
message ...
> 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" > wrote in
>> message ...
>> > 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

>>
>>
>>


  #5  
Old March 31st 08, 09:12 AM posted to microsoft.public.excel.programming
Trevor Williams
external usenet poster
 
Posts: 181
Default XL2002: XY Chart and Image Position...

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" > wrote in
> message ...
> > 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" > wrote in
> >> message ...
> >> > 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
> >>
> >>
> >>

>
>

  #6  
Old March 31st 08, 10:48 AM posted to microsoft.public.excel.programming
Peter T
external usenet poster
 
Posts: 5,600
Default XL2002: XY Chart and Image Position...

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" > wrote in
message ...
> 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" > wrote in
> > message ...
> > > 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" > wrote in
> > >> message ...
> > >> > 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
> > >>
> > >>
> > >>

> >
> >



  #7  
Old April 1st 08, 02:20 PM posted to microsoft.public.excel.programming
Jon Peltier
external usenet poster
 
Posts: 6,582
Default XL2002: XY Chart and Image Position...

I think "'frame' of the shape" means the rectangle circumscribing the shape,
like the square that's outlined by resizing handles when a circle is
selected.

It might be more robust to determine the coordinates of the mouse click, and
use algebraic algorithms to determine what mapped region the click occurred
within.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions> wrote in message
...
> 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" > wrote in
> message ...
>> 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" > wrote in
>> > message ...
>> > > 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" > wrote
>> > >> in
>> > >> message ...
>> > >> > 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
>> > >>
>> > >>
>> > >>
>> >
>> >

>
>



  #8  
Old April 1st 08, 04:26 PM posted to microsoft.public.excel.programming
Peter T
external usenet poster
 
Posts: 5,600
Default XL2002: XY Chart and Image Position...

Trevor sent me his file. In effect he has a web-like grid of trapezoids and
wants to know which part of the web, ie trapezoid, his XY point is over. The
trapezoids are smaller than the rectangular shapes that contain them, it's
the parts of the rectangle that surround the trapezoids that overlap and
cause problems

Although the macro I posted can determine if the chart point is over
multiple shapes there's no way to determine which is the 'required' shape.
No way to set ZOrders in a consistent way.

Much better to dispense with the chart altogether, some trig and algorithms,
as you say, and do all with shapes. I've sent something to the OP along
those lines.

It was a great little macro (modified slightly from the original as posted).
But as it turns out it has no practical purpose, just as I thought! Or maybe
it might still have life for determining the position of any chart item
without tedious calculation from first principles.

Regards,
Peter T

"Jon Peltier" > wrote in message
...
> I think "'frame' of the shape" means the rectangle circumscribing the

shape,
> like the square that's outlined by resizing handles when a circle is
> selected.
>
> It might be more robust to determine the coordinates of the mouse click,

and
> use algebraic algorithms to determine what mapped region the click

occurred
> within.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Peter T" <peter_t@discussions> wrote in message
> ...
> > 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" > wrote in
> > message ...
> >> 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" > wrote in
> >> > message ...
> >> > > 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" > wrote
> >> > >> in
> >> > >> message ...
> >> > >> > 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
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >

> >
> >

>
>



  #9  
Old April 1st 08, 04:42 PM posted to microsoft.public.excel.programming
Jon Peltier
external usenet poster
 
Posts: 6,582
Default XL2002: XY Chart and Image Position...

I was going to say, scrap the shapes and do it all on the chart. Define the
polygons using the XY coordinates of the points, color them in if you like
using

http://peltiertech.com/Excel/Charts/VBAdraw.html

then use the algorithms here to determine which polygon contains the cursor
location:

http://andypope.info/ngs/ng5.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions> wrote in message
...
> Trevor sent me his file. In effect he has a web-like grid of trapezoids
> and
> wants to know which part of the web, ie trapezoid, his XY point is over.
> The
> trapezoids are smaller than the rectangular shapes that contain them, it's
> the parts of the rectangle that surround the trapezoids that overlap and
> cause problems
>
> Although the macro I posted can determine if the chart point is over
> multiple shapes there's no way to determine which is the 'required' shape.
> No way to set ZOrders in a consistent way.
>
> Much better to dispense with the chart altogether, some trig and
> algorithms,
> as you say, and do all with shapes. I've sent something to the OP along
> those lines.
>
> It was a great little macro (modified slightly from the original as
> posted).
> But as it turns out it has no practical purpose, just as I thought! Or
> maybe
> it might still have life for determining the position of any chart item
> without tedious calculation from first principles.
>
> Regards,
> Peter T
>
> "Jon Peltier" > wrote in message
> ...
>> I think "'frame' of the shape" means the rectangle circumscribing the

> shape,
>> like the square that's outlined by resizing handles when a circle is
>> selected.
>>
>> It might be more robust to determine the coordinates of the mouse click,

> and
>> use algebraic algorithms to determine what mapped region the click

> occurred
>> within.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> ...
>> > 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" > wrote in
>> > message ...
>> >> 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" > wrote
>> >> > in
>> >> > message ...
>> >> > > 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" >
>> >> > >> wrote
>> >> > >> in
>> >> > >> message
>> >> > >> ...
>> >> > >> > 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
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >
>> >

>>
>>

>
>



  #10  
Old April 2nd 08, 04:13 PM posted to microsoft.public.excel.programming
Peter T
external usenet poster
 
Posts: 5,600
Default XL2002: XY Chart and Image Position...

I had another look at this, based on what you suggest below and the file I
sent you off-line yesterday. The web like structure can be replicated with
series quite simply; in essence concentric octagons, 8 XY lines per series,
then XY lines intersecting equivalent 'corner' points of the hexagons. That
makes a large number of polygons, so far so good, except the polygons are
described by intersecting points in "different" series.

Would take quite a lot, I think, to work out which points in which series
describe the polygon that sits over a given XY position, with which to go on
and build a freeform over the points (or re-design an existing freeform as
required).

Regards,
Peter T


"Jon Peltier" > wrote in message
...
> I was going to say, scrap the shapes and do it all on the chart. Define

the
> polygons using the XY coordinates of the points, color them in if you like
> using
>
> http://peltiertech.com/Excel/Charts/VBAdraw.html
>
> then use the algorithms here to determine which polygon contains the

cursor
> location:
>
> http://andypope.info/ngs/ng5.htm
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Peter T" <peter_t@discussions> wrote in message
> ...
> > Trevor sent me his file. In effect he has a web-like grid of trapezoids
> > and
> > wants to know which part of the web, ie trapezoid, his XY point is over.
> > The
> > trapezoids are smaller than the rectangular shapes that contain them,

it's
> > the parts of the rectangle that surround the trapezoids that overlap and
> > cause problems
> >
> > Although the macro I posted can determine if the chart point is over
> > multiple shapes there's no way to determine which is the 'required'

shape.
> > No way to set ZOrders in a consistent way.
> >
> > Much better to dispense with the chart altogether, some trig and
> > algorithms,
> > as you say, and do all with shapes. I've sent something to the OP along
> > those lines.
> >
> > It was a great little macro (modified slightly from the original as
> > posted).
> > But as it turns out it has no practical purpose, just as I thought! Or
> > maybe
> > it might still have life for determining the position of any chart item
> > without tedious calculation from first principles.
> >
> > Regards,
> > Peter T
> >
> > "Jon Peltier" > wrote in message
> > ...
> >> I think "'frame' of the shape" means the rectangle circumscribing the

> > shape,
> >> like the square that's outlined by resizing handles when a circle is
> >> selected.
> >>
> >> It might be more robust to determine the coordinates of the mouse

click,
> > and
> >> use algebraic algorithms to determine what mapped region the click

> > occurred
> >> within.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> ...
> >> > 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" > wrote in
> >> > message ...
> >> >> 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" > wrote
> >> >> > in
> >> >> > message ...
> >> >> > > 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" >
> >> >> > >> wrote
> >> >> > >> in
> >> >> > >> message
> >> >> > >> ...
> >> >> > >> > 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
> >> >> > >>
> >> >> > >>
> >> >> > >>
> >> >> >
> >> >> >
> >> >
> >> >
> >>
> >>

> >
> >

>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to change position of chart labels on line chart Shane Henderson[_2_] Charts and Charting in Excel 1 May 27th 11 09:31 AM
xl2002: Position Picture in SeriesCollection Trevor Williams Excel Programming 10 October 23rd 07 04:02 PM
Is there any way to search image of my local drive with Image name or Image Title, Description [email protected] Excel Programming 3 August 3rd 07 01:44 PM
get cursor position when clicking on an image timspier Excel Programming 2 March 3rd 06 04:15 AM
Changing chart title changes position of chart Bing Excel Programming 3 March 9th 05 08:11 AM


All times are GMT +1. The time now is 08:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.