Shapes with boundaries tied to chart data points

  • Thread starter Thread starter Ken Paul
  • Start date Start date
K

Ken Paul

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
 
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 >
 
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
 
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
 
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
 
Back
Top