Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Problem accessing Series properties (2000/2003)

    We are just resetting a load of Chart range reference for the coming financial year and via the Chart Wizard have started pointing the This Year ranges to a newly allocated block of 52 cells. This is fine, but tiresome. I was hoping to automate some of the process but there seems to be a very serious bug in Excel 2000/2003 in the Chart objects hierarchy in that if a Series has its Values pointing to a blank range of cells (so that nothing is currently visible for that series in the Chart Area) then you cannot access the Series properties via VBA - e.g. trying to access Series.Name and Series.Values gives errors such as "unable to get/set object property".

    This is especially annoying as chart.SeriesCollection.Count returns the correct number of series for the relevant Chart, and obviously all the series in question (with their Names and Values) are visible via the Source Data... button of the Chart Wizard. The macro recorder will happily record assignments to the series values via the Wizard, but replaying the macro or trying to assign a new range in VBA fails with the above errors. Macros will work happily once the data series is actually displayed in the Chart Area (range contains at least one non-blank cell).

    Does anyone know of a cause/workaround for this, as I have 74 Charts to amend in one sheet (and have already run up against the Autoscaling/too many fonts problem!!)?

    Thanks

    Jeremy


    Does anytion

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

    Re: Problem accessing Series properties (2000/2003)

    It depends on the chart type. In a column chart, for example, you have this problem, but not in an area chart. A workaround is to change the chart type temporarily. Here is a simple example:

    With ActiveChart
    .ChartType = xlArea
    .SeriesCollection(2).Values = ActiveSheet.Range("E2:E53")
    .ChartType = xlColumnClustered
    End With

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Problem accessing Series properties (2000/2003)

    Thanks Hans. The MS KB also suggests changing the Plot blanks as zeros option (or whatever it's called) to force the data series to be displayed (albeit as a line of zeros) so I'll try both methods. Annoying that the problem has persisted into Excel 2003 at least!!

Posting Permissions

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