CODEWITHSUNDEEP
excelvba
skatun
skatun

Reputation: 877

Resize plot area in embedded chart object

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

Upvotes: 2

Views: 1693

Answers (2)

Cheries Mewengkang
Cheries Mewengkang

Reputation: 76

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

Upvotes: 0

skatun
skatun

Reputation: 877

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

Upvotes: 3

Related Questions