Hi Andy,

Thnaks so much for the help on this. Its nearly there, there is however an
offset in the chart/column line up which appears to get worse as you look at
the categories from left to right. Is this anything you would be able to help
with or is this a limitation with how the cells / chart dimensions are
measured.

Thanks,

G

"Andy Pope" wrote:

Hi,

You could try this.
The chart will not be resize. Only it's top left position will be change to
align with the
anchor cell E4.

'------------------------------------------
Sub AlignColsWithChartCols()
'
' Attempt to align worksheet columns with chart
' columns
'
Dim rngAlign As Range
Dim chtTemp As ChartObject
Dim sngColWidth As Single
Dim lngPointIndex As Long

' top left position for chart
Set rngAlign = Range("E4")
Set chtTemp = ActiveSheet.ChartObjects(1)
Application.ScreenUpdating = False

With chtTemp
.Left = rngAlign.Left
.Top = rngAlign.Top
.Placement = xlFreeFloating
With .Chart
sngColWidth = .PlotArea.InsideWidth /
.SeriesCollection(1).Points.Count
End With
End With

' Adjust gap between chart and plotarea
m_AdjustColWidth rngAlign, _
(chtTemp.Chart.ChartArea.Left +
chtTemp.Chart.PlotArea.InsideLeft)
Set rngAlign = rngAlign.Offset(0, 1)

' adjust columns
For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count
m_AdjustColWidth rngAlign, sngColWidth
Set rngAlign = rngAlign.Offset(0, 1)
Next
Application.ScreenUpdating = True

End Sub

Private Sub m_AdjustColWidth(Col As Range, Size As Single)

Col.ColumnWidth = 1
Do While (Col.Offset(0, 1).Left - Col.Left) < Size
Col.ColumnWidth = Col.ColumnWidth + 1
Loop
Do While (Col.Offset(0, 1).Left - Col.Left) Size
Col.ColumnWidth = Col.ColumnWidth - 0.1
Loop

End Sub
'------------------------------------------

Obviously run this on a test file.
Please post back and let me know if it worked for your chart.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"GS80" wrote in message
...
Hi,

I'm working with the Excel 2007 object model. I am trying to create an
output sheet where i can position data and cells side by side in a
specific
order. The problem i am having is when trying to relate column widths with
chart objects. I want to be able to get the size of the plot area and then
size a cell or multiple cells based on this value. I then plan to move the
chart into a position over the columns so that data above and below the
chart
lines up as i want it. How do the column sizes relate to the chart sizing?

Any help on this would be greatly appreciated.

G