Setting chart's plot area to the correct size and keeping it centered with VBA?

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
348
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
The sizing mechanic for the plot area of my chart is acting very bizarre. If I manually click through the script a step at a time it will look like I want it to, which is like the picture below, but if I run it normally it will end up looking shrunken and off-center. Is there a way to improve my current script to make this correct regardless of those circumstances?

VBA Code:
Set pieChart = coSht.Shapes.AddChart2(251, xlPie)
       
        pieChart.ScaleWidth 1.5, msoFalse, msoScaleFromTop
        pieChart.ScaleHeight 1.5, msoFalse, msoScaleFromTop
       
        With pieChart.Chart
       
            .SetSourceData Source:=trnRng
            .ClearToMatchStyle
            .ChartStyle = 257
            .SetElement (msoElementChartTitleAboveChart)
           
            With .ChartTitle
           
                .Orientation = xlUpward
                .Left = 6.954
                .Top = 76
                .Text = "Period: " & period
               
                With .Format.TextFrame2.TextRange.Characters(1, .Characters.Count)
               
                    .ParagraphFormat.TextDirection = msoTextDirectionLeftToRight
                    .ParagraphFormat.Alignment = msoAlignCenter
                   
                With .Font
               
                    .Bold = msoTrue
                    .Fill.Visible = msoTrue
                    .Fill.ForeColor.RGB = RGB(242, 242, 242)
                    .Fill.Transparency = 0
                    .Fill.Solid
                    .Size = 16
                    .Spacing = 1
                   
                End With
               
           End With
          
        End With

        pieChart.Chart.FullSeriesCollection(1).ApplyDataLabels
        With pieChart.Chart.SeriesCollection(1).DataLabels
       
            .Font.Size = 11
            .Font.Bold = True
            .Separator = WorksheetFunction.Unichar(10) & "V: "
            .ShowCategoryName = True
           
        End With

        With pieChart.Chart.PlotArea
           
            .Top = 10.8
            .Left = 0
            .Height = 375

        End With

   
        End With

        For Each srs In pieChart.Chart.SeriesCollection
            With srs
                If .HasDataLabels Then
                    nPts = .Points.Count
                    aVals = .Values
                    For iPts = 1 To nPts
                        If aVals(iPts) = 0 Then
                            .Points(iPts).HasDataLabel = False
                        End If
                    Next
                End If
            End With
        Next
       
    End If
    pieChart.Chart.PlotArea.Left = 125

1634787258433.png



This is how it looks if the script is run without stepping through it:

1634787397384.png
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,215,063
Messages
6,122,934
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top