NewSeries only add one point to a chart...

[marklenel]

Hi folks,

The is a piece of code recorded directly from Excel in creating a bubble chart with 17 points. Using the chart dialogue box ('Source data') works fine i.e. 17 points added. However, running this code by itself results in a chart with only 1 point! Even wierder, going back into the source data... dialogue box, all the references for the new series are correct, pointing to 17 rows of data.

Any ideas why it's doing this, or workarounds would be most welcome!

Thanks.

Sub Macro1()

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C2:R18C2"
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3:R18C3"
    ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C1"
    ActiveChart.SeriesCollection(1).BubbleSizes = "=Sheet1!R2C4:R18C4"
    ActiveChart.ChartType = xlBubble3DEffect

End Sub

 

[SkipVought]

What bubble sizes are you using in your points?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[marklenel]

Bubble sizes are currently between 0.3 and 0.7. I don't think that matters too much - Excel chooses a scaling that suits all the bubbles on the chart.

However, having just changed one cell in the bubble size range, all points on the chart suddenly appear!!!

It appears that the series is being created correctly, just the visual aspect of the chart is not being refreshed/updated...!?!?!? Another of Microsoft's little oddities, or is there a reason for this...???

 

[SkipVought]

VOLA!

I don't understand either, but fiddling with the range seemed to "solve" the conumdrum.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[marklenel]

Wierd, isn't it. Still trying to replicate the 'tweak' in code, though, as it's still sticking.

It's still only showing 1 bubble/point, so trying to manipulate the DataLabels for point 2 etc. make it go Pete Tong.