Excel 2007 VBA  - Plot Area size not working

Posted on 2010-01-01
Last Modified: 2012-06-27
  I have a worksheet with multiple types of charts (bar, line, pie). I have created VBA code that adjusts the size of the plot area when the back end data is refreshed. This is necessary because for some reason the charts plot area size will not stay after the data is refreshed, or after the workbook is closed, and re-opened. My code for the bar, and line charts works fine. The pie charts do not want to work with my code though.

Oddly enough, if I change the chart size manually in the worksheet, then run my code, the size stays, but running the code beforehand does nothing. Here is an example of my code:

The code seems to work fine in 2003. Right now I am using 2007 in compatibility mode, this is where I am having the problem.

Any help is greatly appreciated!
'pie charts

For a = 17 To 20

ActiveSheet.ChartObjects("Chart" & a).Activate

With ActiveChart

.PlotArea.Height = 208

.PlotArea.Top = 62

.PlotArea.Width = 208

.PlotArea.Left = 121

end with


Open in new window

Question by:jm302
    LVL 10

    Expert Comment

    What is the Pie Chart Number?

    Accepted Solution

       Thanks for the suggestion, but the charts in question are all named Chart17 to 20. I actually came up with a result. It seems like it was just the loop itself that was causing the problem. I ended up just writing out the code for each individual chart and that fixed the problem. I guess the loops were just too fast for the charts.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now