Resize plot area in embedded chart object

Question

I have problem resizing plot area in an embedded chart in excel

Dim myChart As Chart

maxPie = ThisWorkbook.Sheets(sheetName).Range("A1048576").End(xlUp).Row

Set myChart = ThisWorkbook.Sheets(sheetName).Shapes.AddChart.Chart


myChart.ChartType = xlBarClustered
myChart.SetSourceData Source:=Range(sheetName & "!$A$5:$C$" & maxPie)

With myChart.Parent
    .Top = 10
    .Left = 500
    .Width = 500
    .Height = 500
End With

With myChart.PlotArea
    .Top = 70
    .Height = 420
End With

if i press debug and then F5 then it resizes it, do I need to add a delay in my code because its not finished generating the plot area before I try to resize it

This kind of code can be a bit buggy. Sometimes adding a DoEvents call, or reading the .Top value into a variable first can help. — Rory
– Rory, Commented Mar 8, 2017 at 13:33

skatun · Accepted Answer · 2017-03-08 14:25:22Z

3

The comment Rory made about reading the value solved the issue, strange that this is needed though..

Dim temp As Integer
With myChart.PlotArea
    temp = .Top
    temp = .Height
    .Top = 70
    .Height = 420
End With

answered Mar 8, 2017 at 14:25

skatun

8774 gold badges20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cheries Mewengkang · Accepted Answer · 2017-03-08 14:46:32Z

0

I think the problem why your code return error because PlotArea properies can only be modify after the Chart object if fully loaded. So yes, you need to complete Chart Object loading process and modify any PlotArea properties. The code below will work. Try it..!

Option Explicit

Public Sub Demo()

    Dim maxPie As Long
    Dim myChart As Chart

    'I assume that your chart is on Sheet1
    maxPie = Sheet1.Range("A1048576").End(xlUp).Row
    Set myChart = Sheet1.Shapes.AddChart2.Chart

    With myChart
        .ChartType = xlBarClustered
        .SetSourceData Source:=Range("Sheet1!$B$2:$C$" & maxPie)
    End With

    With myChart.Parent
        .Top = 10
        .Left = 500
        .Width = 500
        .Height = 500
    End With

    'Delay the SetPlotArea code execution using OnTime function
    Application.OnTime Now, "SetPlotArea", Now + TimeValue("0:0:5")

End Sub

Public Sub SetPlotArea()
    Dim ch As Chart
    Set ch = Sheet1.ChartObjects(1).Chart

    ch.PlotArea.Top = 70
    ch.PlotArea.Height = 420
End Sub

answered Mar 8, 2017 at 14:46

Cheries Mewengkang

761 silver badge6 bronze badges

2 Comments

skatun Over a year ago

I prefer to pass ch as an object, not sure that is possible with your code, however Rory code worked, and then I do not have to add a fix delay, but your code works aswell:)

Cheries Mewengkang Over a year ago

In programming world there are many ways to solve one problem, so choose the easiest and the fastest way.......Since Rory code's is more simple, I recommend that one.

Collectives™ on Stack Overflow

Resize plot area in embedded chart object

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related