Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Sussex, England
    Thanked 0 Times in 0 Posts

    Chart Sizing VBA (Excel 97)

    I am creating a spreadsheet that has automated charts in it. The chart data is entered into a dialog box on start up. The charts are already in the spreadsheet, the depending on the option chosen they can be different types of chart e.g. Pie, Column or Line. When the data is entered into them, they need to be different sizes depending on the chart type.

    I specify the size and location using the following code:

    Selection.Left = 30
    Selection.Top = 30
    Selection.Width = 400
    Selection.Height = 350

    With different numbers for a Pie chart. The charts also don't always have a Side title so that can make a difference.

    The problem is that the chart comes out too wide with the Side title over the numbers if there is one. The Charts are interactive and when you click them it launches another dialog box to change properties/data of the chart if you want to. The code behind these tells the chart to be the same size and position.

    If you launch this UserForm and keep all the data the same then press OK, the chart changes slightly, then if you repeat launching and keeping the data the same it gets to how I want it to look.

    I thought maybe the code was moving but if you repeat it any more times it stays the same size and position.

    I have tried just adding the code in again to repeat it but it still comes out wrong as before.

    Has anyone ever come across this before? Is it a bug?
    Any suggestions would be very welcome.

    Thanks, Chesney

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Chart Sizing VBA (Excel 97)

    Yes, this is a flaky aspect of charts in Excel 97 (don't have enough experience in other versions). I once wrote a macro for a user who wanted a series of charts to have the same size. I ended up executing the same code three or four times in a loop, in the hope that that was enough to "stabilize" the chart size. In most cases, it was...

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Portland, Oregon, USA
    Thanked 3 Times in 3 Posts

    Re: Chart Sizing VBA (Excel 97)


    Something to experiment with...

    Try setting the ChartArea.AutoScaleFont property to false and see if it makes a difference.


    Jim Cone
    San Francisco, CA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts