I have added several shapes to a standalone chart (in VBA) with the boundary
points defined as data values converted to points in chart coordinates.
Though this looks great on the screen, the boundaries are way out of place
when I resize / reshape the window or print the chart. Is there a way to
tie shape boundaries to coordinates on the chart so that the shape maintains
its relationship to the chart when the window is resized / reshaped or the
chart is printed? I'm using Excel 2002.

Ken -

The only way to ensure that a drawn-on object fits properly when the
chart is resized is to redraw it after resizing the chart. To assure a
shape that fit when the chart was finished will fit when printed, don't
print the chart, select the underlying range of cells and print that
range. (I've been down this road.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Ken Paul wrote:
I have added several shapes to a standalone chart (in VBA) with the boundary
points defined as data values converted to points in chart coordinates.
Though this looks great on the screen, the boundaries are way out of place
when I resize / reshape the window or print the chart. Is there a way to
tie shape boundaries to coordinates on the chart so that the shape maintains
its relationship to the chart when the window is resized / reshaped or the
chart is printed? I'm using Excel 2002.

if it's "loose" shapes you're working with that's going to be a
nightmare :(

can't you use datalabels?

they're connected to the data and can be formatted/positioned relative
to the data they belong to.

have a look at the DataLabel object in vba help.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Paul" wrote:

I have added several shapes to a standalone chart (in VBA) with the
boundary points defined as data values converted to points in chart
coordinates. Though this looks great on the screen, the boundaries are
way out of place when I resize / reshape the window or print the
chart. Is there a way to tie shape boundaries to coordinates on the
chart so that the shape maintains its relationship to the chart when
the window is resized / reshaped or the chart is printed? I'm using
Excel 2002.

Thank you for the response. The problem with this is that the chart is in
an independent sheet, not overlaying cells in a worksheet. I create it in a
complex workbook with many sheets and large VBA modules, then copy the one
chart to a new workbook with no modules for wider distribution. Some
recipients just view the resulting chart on their screen, others print the
resulting chart in various paper sizes. Everything resizes properly except
the shapes I create to shade and label various sections of the chart -- the
shapes are tied to multiple datapoints. Is there an event I could use to
re-draw the shapes when the screen size or paper size changes to redraw the
shapes?

Ken

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

The only way to ensure that a drawn-on object fits properly when the
chart is resized is to redraw it after resizing the chart. To assure a
shape that fit when the chart was finished will fit when printed, don't
print the chart, select the underlying range of cells and print that
range. (I've been down this road.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Ken Paul wrote:
I have added several shapes to a standalone chart (in VBA) with the
boundary
points defined as data values converted to points in chart coordinates.
Though this looks great on the screen, the boundaries are way out of
place
when I resize / reshape the window or print the chart. Is there a way
to
tie shape boundaries to coordinates on the chart so that the shape
maintains
its relationship to the chart when the window is resized / reshaped or
the
chart is printed? I'm using Excel 2002.

Thank you for the response. I'm already using datalabels in the chart, and
they're working properly. The shapes I'm talking about are tied to multiple
datapoints. Their purpose is to shade and label various sections of the
chart. I create the chart in a complex workbook with many sheets and large
VBA modules, then copy the one chart to a new workbook with no modules for
wider distribution. Some recipients just view the resulting chart on their
screen, others print the resulting chart in various paper sizes. Everything
resizes properly except these shapes. What's needed is a way to define
shapes with boundary points tied to data values rather than to pixel
locations.

Ken

"keepitcool" wrote in message
...

if it's "loose" shapes you're working with that's going to be a
nightmare :(

can't you use datalabels?

they're connected to the data and can be formatted/positioned relative
to the data they belong to.

have a look at the DataLabel object in vba help.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Paul" wrote:

I have added several shapes to a standalone chart (in VBA) with the
boundary points defined as data values converted to points in chart
coordinates. Though this looks great on the screen, the boundaries are
way out of place when I resize / reshape the window or print the
chart. Is there a way to tie shape boundaries to coordinates on the
chart so that the shape maintains its relationship to the chart when
the window is resized / reshaped or the chart is printed? I'm using
Excel 2002.

Ken,

Perhaps you're aware of this already, but if you create a drawing
object inside the chart (i.e. while the chart window is active or
selected), afterwards it will resize the shape within the chart to
scale. You could revise your VBA to add the shapes to the
ActiveChart. If you create the drawing object on the worksheet, it
will be difficult to have it adjust automatically. HTH

Jason Goto
AnalysisWorks Inc
Evidence-Based Management Consulting
www.analysisworks.net

"Ken Paul" wrote in message ...
Thank you for the response. I'm already using datalabels in the chart, and
they're working properly. The shapes I'm talking about are tied to multiple
datapoints. Their purpose is to shade and label various sections of the
chart. I create the chart in a complex workbook with many sheets and large
VBA modules, then copy the one chart to a new workbook with no modules for
wider distribution. Some recipients just view the resulting chart on their
screen, others print the resulting chart in various paper sizes. Everything
resizes properly except these shapes. What's needed is a way to define
shapes with boundary points tied to data values rather than to pixel
locations.

Ken

"keepitcool" wrote in message
...

if it's "loose" shapes you're working with that's going to be a
nightmare :(

can't you use datalabels?

they're connected to the data and can be formatted/positioned relative
to the data they belong to.

have a look at the DataLabel object in vba help.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Paul" wrote:

I have added several shapes to a standalone chart (in VBA) with the
boundary points defined as data values converted to points in chart
coordinates. Though this looks great on the screen, the boundaries are
way out of place when I resize / reshape the window or print the
chart. Is there a way to tie shape boundaries to coordinates on the
chart so that the shape maintains its relationship to the chart when
the window is resized / reshaped or the chart is printed? I'm using
Excel 2002.