Positioning of PlotArea in Chart

[mikeymay]

I am using the following code to position a plot area within a chart so that the X axis values line up with a table that I have underneath the chart

Sub CoverRangeWithAChart()

Dim rngChart As Range
Dim rngPlot As Range
Dim objChart As ChartObject
Dim objPlot As PlotArea

Set objChart = ActiveSheet.ChartObjects("Chart 1")
Set objPlot = objChart.Chart.PlotArea

Set rngChart = ActiveSheet.Range(Range("D7"), Range("D7").Offset(18, Range("GraphColCount") + 2))

objChart.Height = rngChart.Height
objChart.Width = rngChart.Width
objChart.Top = rngChart.Top
objChart.Left = rngChart.Left

Set rngPlot = ActiveSheet.Range(Range("E8"), Range("E8").Offset(15, Range("GraphColCount")))

objPlot.Height = rngPlot.Height
objPlot.Width = rngPlot.Width
objPlot.Top = rngPlot.Top
objPlot.Left = rngPlot.Left

End Sub

GraphColCount = 36 - Number of columns which I want the chart to be spread over

I can't understand why the 'Offset' of 15 & 18 don't work as they don't seem to set the bottom of the chart area to 18 rows below E7 and the same with the plot area.


Thanks

 

[pbornemeier]

The PlotArea is located inside the ChartArea which is located inside a Shape which is positioned somewhere on a worksheet.

If there is only 1 shape on the worksheet then you can use
ActiveSheet.Shapes(1).Left to determine the left edge of the chart.
which will be the same as
ActiveSheet.ChartObjects(1).Left
You can set those properties to move the left edge of the chart.

The ChartArea .Left and .Top are referenced to the edge of the Shape that contains the chart, and is just a few units inside that shape (3 in my case).

The PlotArea .Left and .Top are also referenced to the edge of the Shape that contains the chart,

I believe you have to modify the last 2 statements to this:

    objPlot.Top = rngPlot.Top - ActiveSheet.ChartObjects("Chart 1").Top
    objPlot.Left = rngPlot.Left - ActiveSheet.ChartObjects("Chart 1").Left

And perhaps you should set objPlot .InsideHeight and .InsideWidth instead of .Height and .Width. These properties do not include axis labels.

I cannot tell which would give the best alignment.

 

[mikeymay]

I have managed to work out a solution for this. I had a problem with your code pbornemeier as the column in the left side of the chart was place was a different width to the rest. Anyway, code that works for me

Sub CoverRangeWithAChart()

Dim rngChart As Range
Dim rngPlot As Range
Dim objChart As ChartObject
Dim objPlot As PlotArea

Set objChart = ActiveSheet.ChartObjects("Chart 1")
Set objPlot = objChart.Chart.PlotArea

Set rngChart = ActiveSheet.Range(Range("D7"), Range("D7").Offset(18, Range("GraphColCount") + 2))

objChart.Height = rngChart.Height
objChart.Width = rngChart.Width
objChart.Top = rngChart.Top
objChart.Left = rngChart.Left

objPlot.Top = 0
objPlot.Left = 49
objPlot.Height = 191
objPlot.Width = objPlot.Left + ((Range("GraphColCount") + 1) * 23) '+1 is for column D in which the graph is placed

End Sub