Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Recording sub to format charts (Excel 2002)

    In the attached workbook I have recorded a macro, called "MakeCharts", which runs some data and creates three charts, sending
    them to a dedicated worksheet. It works fine, except I'm stuck on two points:

    1) I never want there to be more than three charts. So how can I change to the macro so that each time it runs, it first goes to the
    dedicated worksheet for charts and deletes any charts there before creating new ones?

    2) I also wanted the macro to format these charts, but I'm having trouble naming them in a way which works in VBA. The first time
    the macro runs, it creates 3 ChartObjects, Chart1, Chart2 and Chart3. The next time it runs, it creates Chart4, Chart5 and Chart6, so
    it never finds what it's supposed to format. How can I debug this? I'd appreciate any help . (Note: For now, since it's buggy, I've split the formatting code into its own sub, "ChartFormat".)

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

    Re: Recording sub to format charts (Excel 2002)

    1) You can delete any charts on the 'charts space' sheet by executing

    Worksheets("charts space").ChartObjects.Delete

    2) You can name the charts: after changing their location to 'charts space', use code like this:

    With ActiveChart
    .Parent.Name = "Chart1"
    .HasTitle = True
    ...
    End With

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Recording sub to format charts (Excel 2002)

    Thanks Hans. I think, however, I'm doing something wrong. I'm pasting in the code you gave for 2), (changing the names to Chart1, Chart2 and Chart3), but it doesn't compile -- the error message says that the "[/v]" is unexpected. The revised code is attached. What do you think?

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

    Re: Recording sub to format charts (Excel 2002)

    The [/v] was an error in my reply, I corrected it immediately after posting. Simply delete the [/v] from your code.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Recording sub to format charts (Excel 2002)

    Thanks Hans, it works fine now.

    Regards,

    Lingyai

Posting Permissions

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