How to Get a Chart?s Next Available Series Number

[Cyberdude]

I?ve written a macro that will add a new Series to any of my charts. It works. However, in its present version, I do a user prompt to get the next available Series number to assign to the new Series being created.

I currently determine this number manually by inspecting the Series list in the Source Data dialog box before running the macro. To determine the next available Series number I visually count the number of existing Series in the Series list window of the diaglog box. If there are 6 names already there, then the next available one must be 7.

Are there any VBA statements I can use to count the number of Series names in that window. I have a rather complicated method involving a class module and chart events that I?d prefer not to use because I?d have to set it up for each of my workbooks.

[HaHoBe]

Hi, Cyberdude,

maybe something like

Debug.Print ActiveChart.SeriesCollection.Count
shows the number of Series, so
Debug.Print ActiveChart.SeriesCollection.Count + 1
will show the number of the next Series available. In the given examples the chart is activated but you should be able to work around activating by using objects.

Ciao,
Holger

[johnske]

Try this http://xlvba.3.forumer.com/index.php?showtopic=206 (you select the chart and use right-click menu to get its ID - does all shapes also & you can keep it in PERSONAL.XLS to use at any time)

[Cyberdude]

Hey, Holger, thanks a lot pal! :friends: That's exactly what I was looking for. Works like a charm!

John, I looked at your reference, and it looks very interesting, but it'll take me awhile to digest it, which I fully intend to do. Thanx for the reply.
Sid