Embedded chart alignment

[Matt McQueen]

I am trying to make the y-axis of an embedded chart line up with the edge of
a column. However I'm struggling to do so because I'm not sure of the
relationships between:

- activechart.parent.*
- activechart.chartarea.*
- activechart.plotarea.*
- activechart.plotarea.inside*

where * denotes a property such as left, width, top or height.

I know that the lower 3 properties are referenced from the parent object
position, but other than that I can't get the axis to sit where I want it.
It's always *slightly* off. I'm also struggling to determine which of these
properties is read-only.

I'm using the chartobjects.add() command to add an area plot with the
..parent dimensions that I'd like and then I'm removing *all* axes
information, including the axial lines themselves. Thus the plot is
essentially a floating area. There are multiple instances of these floating
areas arranged vertically, and I'd like them to all line up properly with
column edges such that I can use columns to delineate years (they are all
time series).

Any explanations on how these properties relate to each other / are
calculated during chart resizing, such that I can control the position of the
axes, would be great.

Cheers,

Matt

 

[Jon Peltier]

This download (a zipped workbook) has code that aligns various elements of
multiple charts:

http://peltiertech.com/Excel/Zips/AlignChartDimensions.zip

It's not particularly well documented, but it may answer some of your
questions.

- Jon

 

[egun]

The code below aligns the intersection of x- and y-axes with the upper left
corner of the chosen cell. Well, at least it worked on this specific example
for me!

One KEY: If you change just about ANYTHING about the chart after this
(fonts, scales, etc), Excel will likely change the locations of plot area,
axes, etc, and that will change the alignment of axes to cell coordinates.

'
' Aligns chart axis intersection to upper left corner of cell.
' Assumes a chart is selected...
' also assumes we want to align X and Y axes
' with cell "E20", just for example purposes.
'
Sub Align_Chart_Axes_to_Cell()
Dim xLoc As Double, yLoc As Double
xLoc = ActiveSheet.Cells(40, 5).Left
yLoc = ActiveSheet.Cells(40, 5).Top
'
With ActiveChart.Parent
.Left = xLoc - ActiveChart.ChartArea.Left -
ActiveChart.PlotArea.InsideLeft
.Top = yLoc - ActiveChart.ChartArea.Top
.Top = .Top - ActiveChart.PlotArea.InsideHeight
.Top = .Top - ActiveChart.PlotArea.InsideTop
End With
End Sub


HTH,

Eric

 

[Matt McQueen]

Jon, Eric - many thanks to both of you. I think I can sort it out now, and
Eric's clear-headed positioning was what I was struggling in vain to produce.

Cheers,

Matt