Hi Andy,

Thnaks for the quick response. I'll look over the code, need to chage it to
C# anywayand see if this works. What i don't think i stated in the first post
is that i want to line up each category on the X axis with a seperate column
in the background. Not sure if your code does that until i give it a try but
am going to do this next.

Thanks again.

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