Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart Collections (excel 2003)

    I have a chart attached where I run code to add the last data point to a series with some formatting. It works fine in my current workbook, but when I call it into another workbook as an Add-In, it errors out. Can someone explain how to fix this?
    Attached Files Attached Files

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

    Re: Chart Collections (excel 2003)

    I moved the code from your sample workbook to an add-in; it ran without problems.
    What is the error message you get?

  3. #3
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Collections (excel 2003)

    Run time eror 1004: Apply data labels Method fo Point Class failed.

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

    Re: Chart Collections (excel 2003)

    Could you post an example of a workbook with a chart on which the code fails? I can't reproduce the error.

  5. #5
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Collections (excel 2003)

    The workbook is too big to include and I scaled down what i could. However, I did find out what was failing- the pts.count is showing a number of points for a previous set of data I had for the chart- almost like it was caching. I followed it in the watch and the label cannot be applied because the old pts.coutn value was larger than the new one. It there a garbage colelctor for chart series?

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

    Re: Chart Collections (excel 2003)

    In the line

    With pts(pts.Count)

    pts refers to the current series, so I don't understand how pts.Count could refer to a previous run.

    Try this version, it's slightly cleaner:

    Sub LabelLast(ByVal sheetName As Variant, chartName As String)
    Dim pts As Points

    Set pts = ActiveWorkbook.Worksheets(sheetName) _
    .ChartObjects(chartName).Chart.SeriesCollection(1) .Points
    With pts(pts.Count)
    .ApplyDataLabels Type:=xlShowValue
    .MarkerBackgroundColorIndex = xlAutomatic
    .MarkerForegroundColorIndex = xlAutomatic
    .MarkerStyle = xlSquare
    .MarkerSize = 5
    .DataLabel.Position = xlLabelPositionBelow
    .DataLabel.NumberFormat = "0.00""%"""
    End With
    End Sub

Posting Permissions

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