Chart size in relation to column width

[GS80]

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

 

[Andy Pope]

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

 

[GS80]

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

 

[GS80]

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]

You could adjust the value in the m_AdjustColWidth routine so the final
adjustment is smaller.

Also make sure the sheets zoom setting is 100%.

How many columns in your chart?

Cheers
Andy

 

[GS80]

At the moment 5, this is dynamic though. The functions will handle this by
the looks of it.

Changing the precision worked absolutely fine in the adjust function. Thank
you so much for this. It was beginning to be a bit of a bug bear.

G