Non embedded chart location

[Jonathan Davis]

My goal is to create a chart which zooms in to a region of a chart by
the user clicking twice within the chart's boundaries. I have almost
completed my goal, but now I ran into a problem.

Getting the location (in points) of a chart is easy if the chart is
embedded, just get the .top and .left properties of the chartObject
object. With a non-embedded chart, however, there is no chartObject
object.

I can already convert the clicks to points in my axes (many thanks to
Greg Wilson), but they are shifted by the amount of grey area around the
chart. With the correct zooming (I conpensate for that also) and moving
of the chart so that the upper left corner is positioned so that none of
the grey area is showing in the upper left corner then my current code
works perfectly. Given that this is just about the crudest solution that
I have ever heard of, however, I deemed this unacceptable.

So, if anyone could help me find the top and left of a non-embedded
chart, I would be very grateful.

Thanks,
Jonathan Davis

*** Sent via Developersdex http://www.developersdex.com ***

[Leith Ross]

Hello Jonthan,

I saw your post on the Excel Forum. Took a while to get here, but I am
finally here.

To find the chart properties you are interested in you need the Excel
name for the chart, like "Chart 1". The non embedded chart is actually a
Shape object. Shapes are a collection that are part of the worksheet
they are placed on. If your code is on the same worksheet you can use...

With Shapes("Chart 1")
H = .Height
L = .Left
T = .Top
W = .Width
End With

If the code is in a macro or you want to access a chart on a different
worksheet then use...

(Change "Sheet1" to match the worksheet you want)
With Worksheets("Sheet1").Shapes("Chart 1")
H = .Height
L = .Left
T = .Top
W = .Width
End With

Your post at the Excel Forum is
http://www.excelforum.com/showthread.php?t=373131

Sincerely,
Leith Ross


*** Sent via Developersdex http://www.developersdex.com ***

[Jonathan Davis]

The shapes collection only applies to embedded charts. I am looking for
the top and left (in points) of a stand alone (on a separate sheet)
chart.

Thanks,
Jonathan Davis

*** Sent via Developersdex http://www.developersdex.com ***

[Greg Wilson]

I think you're looking for the ActiveWindow.Top and ActiveWindow.Left
properties.

Regards,
Greg Wilson

"Jonathan Davis" wrote:

> My goal is to create a chart which zooms in to a region of a chart by
> the user clicking twice within the chart's boundaries. I have almost
> completed my goal, but now I ran into a problem.
>
> Getting the location (in points) of a chart is easy if the chart is
> embedded, just get the .top and .left properties of the chartObject
> object. With a non-embedded chart, however, there is no chartObject
> object.
>
> I can already convert the clicks to points in my axes (many thanks to
> Greg Wilson), but they are shifted by the amount of grey area around the
> chart. With the correct zooming (I conpensate for that also) and moving
> of the chart so that the upper left corner is positioned so that none of
> the grey area is showing in the upper left corner then my current code
> works perfectly. Given that this is just about the crudest solution that
> I have ever heard of, however, I deemed this unacceptable.
>
> So, if anyone could help me find the top and left of a non-embedded
> chart, I would be very grateful.
>
> Thanks,
> Jonathan Davis
>
> *** Sent via Developersdex http://www.developersdex.com ***
>