New chart series without legend entry

[Soul777Toast]

Hi all,

I'm writing a function to add charts to a spreadsheet based on some user entered parameters, and one of the things I'd like it to do is to be able to handle data columns that are longer than 32000 values. To do this, I am automating a process to create a new series for each 32000 values in a column (which I have got working perfectly). What I am struggling with is being able to set the color formatting for the concurrent series in a column to be the same as the first series, and to create the new series without creating a new entry in the legend. I'm also having some trouble getting it to create the chart as a new sheet (currently it is an object in the active sheet, when I put in the
newchart.chart.location where:=xlLocationAsNewSheet line I get a new chart in a new sheet, but it's just a white blank).

Here's my current code:

[vba]
Function CreateChart(Title As String, ChartData As Range, XMin As Long, XMax As Long, _
YMin As Long, YMax As Long)
Dim NewChart As ChartObject
Dim iColumn As Long

' add the chart
Set NewChart = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)

With NewChart.Chart
' make an XY chart
.ChartType = xlXYScatterSmoothNoMarkers

' add series from selected range, column by column
If ChartData.Rows.Count <= 32000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(ChartData.Rows.Count, 1))
.Name = ChartData.Cells(1, iColumn)
End With
Next iColumn
ElseIf ChartData.Rows.Count > 32000 And ChartData.Rows.Count <= 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(32001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
ElseIf ChartData.Rows.Count > 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(64000, iColumn))
.XValues = ChartData.Range("A32001:A64000")
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(64001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(64001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
End If
End With

With NewChart.Chart.Axes(xlCategory)
.MinimumScale = XMin
.MaximumScale = XMax
End With
With NewChart.Chart.Axes(xlValue)
.MinimumScale = YMin
.MaximumScale = YMax
End With
End Function

[/vba]
Any ideas?

[Bob Phillips]

Can you post a full workbook to save us the work?

[Soul777Toast]

This is just one small part of a much (MUCH) larger unfinished program, and I don't really have time to write a new program as an example for this function. Sorry!

[JonPeltier]

To format series the same, don't use the automatic colors, which change automatically with series number. Explicitly set a color index for each series, which ensures when you set it for like series, they all will match.

When you add a series, you add a legend entry to the chart's legend. You may have to experiment a bit, because there isn't a good linkage between series i and legend entry j, but you should be able to delete the last legend entry added:

Air Code:
[VBA]ActiveChart.Legend.LegendEntry(ActiveChart.Legend.LegendEntries.Count).Dele te[/VBA]

I don't know what's wrong with the Chart.Location command. What if you define a chart variable (not a chart object), and create it using:

[VBA]
Dim NewChart As Chart
Set NewChart = ActiveWorkbook.Charts.Add
[/VBA]

What version of Excel are you using? Sometimes 2007 creates blank charts if no printer is installed.

[Soul777Toast]

Hey, thanks, both of those suggestions worked great (FYI, though, you need to use activechart.legendentries, not activechart.legendentry). Can you tell me what the code is to set the color of a new series?

[JonPeltier]

Told you it was air code.

To get a codee snippet of series color formatting, do what I do. Turn on the macro recorder and format the color of a series.