Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Cambridge, Uppsala
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart macro error (2002)

    Hi, I have been using this macro to size all the charts in a worksheet/book to the same size. My company recently updated Excel to 2002 and now the macro won't work. I get a Compile Error: Syntax error. Can anyone tell me what is wrong? Thanks.

    Sub EqualizeCharts2()
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
    cht.Chart.ChartArea.AutoScaleFont = False
    cht.Height = Application.InchesToPoints(2)
    cht.Width = Application.InchesToPoints(3)
    Next cht
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart macro error (2002)

    Your code runs fine in my Excel 2002 SP3. Check Tools | References in the Visual Basic Editor to see if there is a problem with missing references.
    By the way, which part of the code is highlighted when you get the syntax error?

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Location
    Cambridge, Uppsala
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart macro error (2002)

    Thanks for the response Hans. Oddly References is grayed out in my Tools menu? The part of the macro that is coming up as an error are the first two lines, they appear red. Thanks. Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart macro error (2002)

    Could you attach a small sample workbook that exhibits the problem? You can replace sensitive data with dummy data. Zip the workbook if necessary (size limit for attachments is 100 KB).

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Chart macro error (2002)

    References will be greyed out if you are in Debug mode. Try stopping the code and then checking the references.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Nov 2006
    Location
    Cambridge, Uppsala
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart macro error (2002)

    Thanks Hans, here is a test xls file. Also I did get into references but have not idea which ones should be checked??? Thanks again for your help. Paul
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Chart macro error (2002)

    You have some odd behaviour there! Try removing the module from your workbook, inserting a new one, copying this code into Word, then copying and pasting into your module in Excel:
    <pre>Sub EqualizeCharts2()
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
    cht.Chart.ChartArea.AutoScaleFont = False
    cht.Height = Application.InchesToPoints(2)
    cht.Width = Application.InchesToPoints(3)
    Next cht
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Nov 2006
    Location
    Cambridge, Uppsala
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart macro error (2002)

    Thanks Rory that worked! very strange. Thanks again!

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Chart macro error (2002)

    It may be a sign of corruption creeping into your workbook, so you may want to start thinking about creating a clean copy of it (copying and pasting all the data into a new workbook, exporting the modules form the old workbook and importing them into the new one. If everything else is working, you might be OK as you are though.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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