Rotating a chart, and more..

[adambowie]

I know that a not long ago, someone else asked about rotating charts,
but the solution offered won't work for me.

I have a 2D area chart which currently runs across the x-axis. I'd like
to run the chart along the y-axis instead. This could be achieved by
either rotating the whole chart object, or producing a chart that Excel
by default doesn't seem to let you.

The other part of my problem is that I'd like the said chart to lie
*underneath* the data in a worksheet. And I need the chart to be
dynamic - I don't want to have to export the chart, make it into a jpg
and then import it as a watermark (unless I can automate the whole
routing in VBA).

To give you an idea of what I'm talking about, have a look at 'this'
(http://www.adambowie.com/weblog/archive/chart.jpg) jpg of another
chart taken from a PDF.

Any help or pointers in the right direction would be greatly
appreciated.


Adam

 

[Andy Pope]

Hi,

Firstly the chart, or any other object for that matter, sits on a
drawing layer above the cells. So to get the chart behind the values you
will need to display the values above the chart. This can be done using
textboxes. These textboxes can be linked to cells so they update
automatically.

Getting the actual chart is more of a problem. Try using Jon Peliter's
approach of creating a autoshape to match your data. Obviously this vba
solution will need to be run evrey time your data changes in order to be
dynamic.
http://peltiertech.com/Excel/Charts/VBAdraw.html

Cheers
Andy

 

[adambowie]

Hi Andy,

Thanks for this.

I'm sure I can link textboxes to worksheet values, and I'll have a bas
at the VBA autoshape route. Could be an interesting learning curve! Th
data only changes four times a year, so replotting at those points i
no problem.


Ada