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

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

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

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

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

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

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

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

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

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

Peter -

Looking at the file you forwarded to me, there seems no need for different
colors, so you wouldn't need the freeforms. Even so, I don't think it would
be all that hard to determine the vertices for the freeforms, especially if
the shapes are regular and don't change from time to time. Or if the shapes
will be unchanging, draw them once in a reputable drawing program, and use
the image as the plot area fill of the chart.

- 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
...
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

Hi Jon,

I think there is a need for some means of visually indicating which "hole"
in the "web" contains the XY point.

Had another look (not sure why!) and it can all be done by highlighting the
XYLines between points in the various series that describe the 'hole'. No
shapes (freeforms) required (which would mask the main XY point if filled).

A bit fiddly to derive an algorithm to get the right points in the right
series but once done all works well.

Regards,
Peter T

"Jon Peltier" wrote in message
...
Peter -

Looking at the file you forwarded to me, there seems no need for different
colors, so you wouldn't need the freeforms. Even so, I don't think it
would
be all that hard to determine the vertices for the freeforms, especially
if
the shapes are regular and don't change from time to time. Or if the
shapes
will be unchanging, draw them once in a reputable drawing program, and use
the image as the plot area fill of the chart.

- 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
...
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

Hi! good day! I am working in a very similar project, i have a chart (line chart, date and values of temperatures on that date) and i need to be able to put over the chart a FreeForm (the only free form in the working sheet), once the free form has being draw, and the user is happy with it's shape an position over the graph, the user will run a Macro (button) and it is expected that all points that were cover by the FreeForm (inside the Free Form perimeter) will be identified in a list.

i an relatively new on VBA, it is possible you to email me the example program taht you said it "works" here.
Thanks so much in advance!!!!


On Friday, March 28, 2008 5:59 AM TrevorWilliam wrote:

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


On Friday, March 28, 2008 10:47 AM 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 ...
of


On Friday, March 28, 2008 11:15 AM TrevorWilliam wrote:

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:


On Friday, March 28, 2008 11:57 AM 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 ...


On Monday, March 31, 2008 4:12 AM TrevorWilliam wrote:

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:


On Monday, March 31, 2008 5:48 AM Peter T wrote:

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 ...
and
shape.
well
got
to
address is
work.
require
hence
left
chart
single
important...
top'
will be
will
think
and
through
plot


On Tuesday, April 01, 2008 9:20 AM Jon Peltier wrote:

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
...


On Tuesday, April 01, 2008 11:26 AM Peter T wrote:

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
...
shape,
and
occurred
all
may
could
pixel
a
a
still
You've
not
I
in


On Tuesday, April 01, 2008 11:42 AM Jon Peltier wrote:

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
...


On Wednesday, April 02, 2008 11:13 AM Peter T wrote:

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
...
the
cursor
it's
shape.
click,
not
then
return
the
macro
I
to
If
will
a
'on
chart
returned,
you
see
is


On Wednesday, April 02, 2008 3:54 PM Jon Peltier wrote:

Peter -

Looking at the file you forwarded to me, there seems no need for different
colors, so you wouldn't need the freeforms. Even so, I don't think it would
be all that hard to determine the vertices for the freeforms, especially if
the shapes are regular and don't change from time to time. Or if the shapes
will be unchanging, draw them once in a reputable drawing program, and use
the image as the plot area fill of the chart.

- 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
...


On Thursday, April 03, 2008 6:29 AM Peter T wrote:

Hi Jon,

I think there is a need for some means of visually indicating which "hole"
in the "web" contains the XY point.

Had another look (not sure why!) and it can all be done by highlighting the
XYLines between points in the various series that describe the 'hole'. No
shapes (freeforms) required (which would mask the main XY point if filled).

A bit fiddly to derive an algorithm to get the right points in the right
series but once done all works well.

Regards,
Peter T

"Jon Peltier" wrote in message
...
would
if
shapes
I
with
are
series
go
as
trapezoids
along
Or
item
the
over
case,
top-left
think
it.
it's
offer's
that
control.
(my
would
from
be
with
say
suit
arg2)
is
if
can
graph
that

Hi! good day! I am working in a very similar project, i have a chart (line chart, date and values of temperatures on that date) and i need to be able to put over the chart a FreeForm (the only free form in the working sheet), once the free form has being draw, and the user is happy with it's shape an position over the graph, the user will run a Macro (button) and it is expected that all points that were cover by the FreeForm (inside the Free Form perimeter) will be identified in a list.

i an relatively new on VBA, it is possible you to email me the example program taht you said it "works" here.
Thanks so much in advance!!!!


On Friday, March 28, 2008 5:59 AM TrevorWilliam wrote:

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


On Friday, March 28, 2008 10:47 AM 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 ...
of


On Friday, March 28, 2008 11:15 AM TrevorWilliam wrote:

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:


On Friday, March 28, 2008 11:57 AM 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 ...


On Monday, March 31, 2008 4:12 AM TrevorWilliam wrote:

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:


On Monday, March 31, 2008 5:48 AM Peter T wrote:

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 ...
and
shape.
well
got
to
address is
work.
require
hence
left
chart
single
important...
top'
will be
will
think
and
through
plot


On Tuesday, April 01, 2008 9:20 AM Jon Peltier wrote:

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
...


On Tuesday, April 01, 2008 11:26 AM Peter T wrote:

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
...
shape,
and
occurred
all
may
could
pixel
a
a
still
You've
not
I
in


On Tuesday, April 01, 2008 11:42 AM Jon Peltier wrote:

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
...


On Wednesday, April 02, 2008 11:13 AM Peter T wrote:

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
...
the
cursor
it's
shape.
click,
not
then
return
the
macro
I
to
If
will
a
'on
chart
returned,
you
see
is


On Wednesday, April 02, 2008 3:54 PM Jon Peltier wrote:

Peter -

Looking at the file you forwarded to me, there seems no need for different
colors, so you wouldn't need the freeforms. Even so, I don't think it would
be all that hard to determine the vertices for the freeforms, especially if
the shapes are regular and don't change from time to time. Or if the shapes
will be unchanging, draw them once in a reputable drawing program, and use
the image as the plot area fill of the chart.

- 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
...


On Thursday, April 03, 2008 6:29 AM Peter T wrote:

Hi Jon,

I think there is a need for some means of visually indicating which "hole"
in the "web" contains the XY point.

Had another look (not sure why!) and it can all be done by highlighting the
XYLines between points in the various series that describe the 'hole'. No
shapes (freeforms) required (which would mask the main XY point if filled).

A bit fiddly to derive an algorithm to get the right points in the right
series but once done all works well.

Regards,
Peter T

"Jon Peltier" wrote in message
...
would
if
shapes
I
with
are
series
go
as
trapezoids
along
Or
item
the
over
case,
top-left
think
it.
it's
offer's
that
control.
(my
would
from
be
with
say
suit
arg2)
is
if
can
graph
that


On Monday, March 28, 2011 1:17 PM gus valencia wrote:

Hi! good day! I am working in a very similar project, i have a chart (line chart, date and values of temperatures on that date) and i need to be able to put over the chart a FreeForm (the only free form in the working sheet), once the free form has being draw, and the user is happy with it's shape an position over the graph, the user will run a Macro (button) and it is expected that all points that were cover by the FreeForm (inside the Free Form perimeter) will be identified in a list.



i an relatively new on VBA, it is possible you to email me the example program taht you said it "works" here.

Thanks so much in advance!!!!

That's an old thread! As illustrated in the example I posted it’s possible
to work out if a point (or any/all points on the chart) is under a shape.
However the problem with respect to your objective is any Shape is simply a
rectangle, even though it may enclose some other shape such as a freeform.
If that’s something simple, same could be done but with a bit more work to
calculate the area (eg circle, triangle) within the rectangle. However with
a Freeform that’s going to be a lot more complicated, particularly if it
includes “indents”, even more so if curved lines are allowed.

A different approach would be to get the screen pixel colour over the point.
If it’s same as the Freeform’s fill colour that would indicate the point is
enclosed by the Freeform.

I’m pretty sure that should be doable, however I haven’t actually done it,
and it will take a lot more than five minutes!

Regards,
Peter T

PS similar sent in reply to the email you sent to me off-line


"gus valencia" wrote in message
...
Hi! good day! I am working in a very similar project, i have a chart (line
chart, date and values of temperatures on that date) and i need to be able
to put over the chart a FreeForm (the only free form in the working
sheet), once the free form has being draw, and the user is happy with it's
shape an position over the graph, the user will run a Macro (button) and
it is expected that all points that were cover by the FreeForm (inside the
Free Form perimeter) will be identified in a list.

i an relatively new on VBA, it is possible you to email me the example
program taht you said it "works" here.
Thanks so much in advance!!!!


On Friday, March 28, 2008 5:59 AM TrevorWilliam wrote:

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


On Friday, March 28, 2008 10:47 AM 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 ...
of


On Friday, March 28, 2008 11:15 AM TrevorWilliam wrote:

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:


On Friday, March 28, 2008 11:57 AM 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 ...


On Monday, March 31, 2008 4:12 AM TrevorWilliam wrote:

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:


On Monday, March 31, 2008 5:48 AM Peter T wrote:

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 ...
and
shape.
well
got
to
address is
work.
require
hence
left
chart
single
important...
top'
will be
will
think
and
through
plot


On Tuesday, April 01, 2008 9:20 AM Jon Peltier wrote:

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
...


On Tuesday, April 01, 2008 11:26 AM Peter T wrote:

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
...
shape,
and
occurred
all
may
could
pixel
a
a
still
You've
not
I
in


On Tuesday, April 01, 2008 11:42 AM Jon Peltier wrote:

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
...


On Wednesday, April 02, 2008 11:13 AM Peter T wrote:

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
...
the
cursor
it's
shape.
click,
not
then
return
the
macro
I
to
If
will
a
'on
chart
returned,
you
see
is


On Wednesday, April 02, 2008 3:54 PM Jon Peltier wrote:

Peter -

Looking at the file you forwarded to me, there seems no need
for different
colors, so you wouldn't need the freeforms. Even so, I don't
think it would
be all that hard to determine the vertices for the freeforms,
especially if
the shapes are regular and don't change from time to time. Or
if the shapes
will be unchanging, draw them once in a reputable drawing
program, and use
the image as the plot area fill of the chart.

- 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
...


On Thursday, April 03, 2008 6:29 AM Peter T wrote:

Hi Jon,

I think there is a need for some means of visually indicating
which "hole"
in the "web" contains the XY point.

Had another look (not sure why!) and it can all be done by
highlighting the
XYLines between points in the various series that describe the
'hole'. No
shapes (freeforms) required (which would mask the main XY
point if filled).

A bit fiddly to derive an algorithm to get the right points in
the right
series but once done all works well.

Regards,
Peter T

"Jon Peltier" wrote in
message
...
would
if
shapes
I
with
are
series
go
as
trapezoids
along
Or
item
the
over
case,
top-left
think
it.
it's
offer's
that
control.
(my
would
from
be
with
say
suit
arg2)
is
if
can
graph
that