.PlotArea size changes when a series is added
 

I have created a chart programattically and sized the plot area like this.

With .ActiveChart.PlotArea
.Interior.ColorIndex = Excel.Constants.xlNone
.Width = 385
.Height = 290
.Left = 30
.Top = 20
End With

However, later in my code I add a series to that chart using this code.

With objEx
.ActiveSheet.ChartObjects("Chart " & testcase.index).Select()
seriesnum = .ActiveChart.SeriesCollection.count + 1
.ActiveChart.SeriesCollection.NewSeries()
.ActiveChart.SeriesCollection(seriesnum).Name = strOutSheet
.ActiveChart.SeriesCollection(seriesnum).XValues = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(0) & ":R" & rowspot(1) & "C" & colspot(0)
.ActiveChart.SeriesCollection(seriesnum).Values = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(1) & ":R" & rowspot(1) & "C" & colspot(1)
.ActiveChart.SeriesCollection(seriesnum).Border.Co lorIndex = intColor

'adjust legend size and entry color based on pass/fail status
With .ActiveChart.Legend
If lcheck.pass Then
.LegendEntries(.LegendEntries.Count).Font.ColorInd ex = 10 'green
Else
.LegendEntries(.LegendEntries.Count).Font.ColorInd ex = 3 'red
End If
End With
End With

Why does adding this series change the .PlotArea size/location? I have set
Excel to visible and turned screen updating on so I know the size changes on
this line:

..ActiveChart.SeriesCollection(seriesnum).XValues = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(0) & ":R" & rowspot(1) & "C" & colspot(0)

Any suggestions? This is extremely annoying!!

.PlotArea size changes when a series is added
 

Hi,

The plotarea dimensions can change due to changes in the axis labels.
If either the x or y axis causes addition information to be displayed
the size can change. For example Y values increasing so an extra digit
is required to show new values.

Cheers
Andy

BBauer42 wrote:
I have created a chart programattically and sized the plot area like this.

With .ActiveChart.PlotArea
.Interior.ColorIndex = Excel.Constants.xlNone
.Width = 385
.Height = 290
.Left = 30
.Top = 20
End With

However, later in my code I add a series to that chart using this code.

With objEx
.ActiveSheet.ChartObjects("Chart " & testcase.index).Select()
seriesnum = .ActiveChart.SeriesCollection.count + 1
.ActiveChart.SeriesCollection.NewSeries()
.ActiveChart.SeriesCollection(seriesnum).Name = strOutSheet
.ActiveChart.SeriesCollection(seriesnum).XValues = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(0) & ":R" & rowspot(1) & "C" & colspot(0)
.ActiveChart.SeriesCollection(seriesnum).Values = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(1) & ":R" & rowspot(1) & "C" & colspot(1)
.ActiveChart.SeriesCollection(seriesnum).Border.Co lorIndex = intColor

'adjust legend size and entry color based on pass/fail status
With .ActiveChart.Legend
If lcheck.pass Then
.LegendEntries(.LegendEntries.Count).Font.ColorInd ex = 10 'green
Else
.LegendEntries(.LegendEntries.Count).Font.ColorInd ex = 3 'red
End If
End With
End With

Why does adding this series change the .PlotArea size/location? I have set
Excel to visible and turned screen updating on so I know the size changes on
this line:

.ActiveChart.SeriesCollection(seriesnum).XValues = "='" & strOutSheet &
"'!R" & rowspot(0) & "C" & colspot(0) & ":R" & rowspot(1) & "C" & colspot(0)

Any suggestions? This is extremely annoying!!

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info