Graph plot area shifting

[mikeymay]

I am using the following code to dynamically size a graphs plot area to the same size as the data table below it but I am having problems with the plot area shifting when the table data is refreshed using a routine

Sub CoverRangeWithAChart()

Dim rngChart As Range
Dim rngPlot As Range
Dim objChart As ChartObject
Dim objPlot As PlotArea
Dim intTop As Integer
Dim intHeight As Integer
Dim intLeft As Integer
Dim intMax As Integer
Dim intCvDChartColRange As Integer
Dim rngCvDMaxRange As Range

Range("E31").Select

Do Until Selection.Offset(-2, intCvDChartColRange) = 0
    intCvDChartColRange = intCvDChartColRange + 4
Loop

Set rngCvDMaxRange = Range(Selection, Selection.Offset(3, intCvDChartColRange))

intMax = WorksheetFunction.Max(rngCvDMaxRange) + 1

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


With objChart
    .Activate
    .Select
End With

ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
    .MinimumScale = 0
    .MaximumScale = intMax
    .MinorUnitIsAuto = True
    .MajorUnit = 1
End With

Set rngChart = ActiveSheet.Range(Range("A9"), Range("A9").Offset(18, Range("GraphColCount") + 4))

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

intTop = objPlot.Top
intHeight = objPlot.Height
intLeft = objPlot.Left

objPlot.Width = objPlot.Left + ((Range("GraphColCount") + 1) * 23.25)
objPlot.Left = intLeft
objPlot.Top = intTop
objPlot.Height = intHeight

End Sub

Each time the data is refreshed the objPlot.Left is reducing by 1.

I have tried adding

objPlot.Left = objPlot.Left +1

after

objPlot.Left = intLeft

but this doesn't seem to work.

 

[Andrew Poulsom]

What's causing the data to refresh?

 

[mikeymay]

The data table and graph are refreshed via routine that performs a SQL query which populates the table with data.

The data relates to time slots and dependant on the minimum hour and maximum hour on the returned data, the the data table is sized accordingly and the graph needs to resize to the size of the table. The refresh is preformed via a button on the screen.

 

[Andrew Poulsom]

I'm struggling to understand why the chart's plot area would shift to the left when the data is refreshed. Is that happening automatically? Also, in what way doesn't this work?

objPlot.Left = objPlot.Left +1

 

[mikeymay]

Sorry, don't think I made my issue clear.

The plot areas left value, which needs to be 178, is reducing by 1 when the

objPlot.Left = intLeft

executes.

The value prior to this line executing is 178 but once executed it is 177.....

I'm struggling to understand this as well as the code is very explicit about what I want it to do.....and it doesn't want to play ball!!

 

[mikeymay]

Hmmmm...

I have just tried the following

objPlot.Left = 178

and after this executes the Left value is 177.

Does this code not do what it says on the tin and I need to use somethign else?

 

[Andrew Poulsom]

You can't shift the plot area outside the chart area. When I tried your code on a test chart I could only get to 51. Try temporarily making the width small and setting it back afterwards.

 

[mikeymay]

Ok, managed to for a resolution for this....

I was using the intLeft as the left side graph & table could move depending on a couple of other things which now are no longer requirted, therefore the graph and table will always have the same left point. Solution.....remove the objPlot.Left = intLeft.

Simple again...Doh!!!