Excel SeriesCollection 2003 vs 2010

[Sniperbooya]

Hi guys,

I have come across an issue with one of our sheets, which we (apparently) do not use that often, since i did not hear anything about it..

We have got a graph with a little VBA to add additional series to the graph.

This works perfectly with Office 2003, but 2010 gives us an 'invalid parameter' error which i can not get my head around.

Code:

Private Sub CommandButton3_Click()
Charts("Grafiek1").Unprotect Password:="01FGSlab1962"
With Charts("Grafiek1")
    .HasTitle = True
    .ChartTitle.Text = Range("A8").Text
End With
Charts("Grafiek1").Protect Password:="01FGSlab1962"
If Not Range("I35") = "" Then
Charts("Grafiek1").Unprotect Password:="01FGSlab1962"
Charts("Grafiek1").SeriesCollection.Add _
    Source:=ActiveWorkbook.Worksheets("Zeefanalyse").Range("I35:I41")
    serieslabels = True
    Charts("Grafiek1").SeriesCollection(Range("I35").Value).Border.ColorIndex = 10
    Charts("Grafiek1").SeriesCollection(Range("I35").Value).MarkerBackgroundColorIndex = 10
    Charts("Grafiek1").SeriesCollection(Range("I35").Value).MarkerForegroundColorIndex = 10
    
Charts("Grafiek1").SeriesCollection(Range("I35").Value).XValues = _
        Worksheets("Zeefanalyse").Range("H36:H41")
Charts("Grafiek1").SeriesCollection.Add _
    Source:=ActiveWorkbook.Worksheets("Zeefanalyse").Range("J35:J41")
    serieslabels = True
    Charts("Grafiek1").SeriesCollection(Range("J35").Value).Border.ColorIndex = 3
    Charts("Grafiek1").SeriesCollection(Range("J35").Value).MarkerBackgroundColorIndex = 3
    Charts("Grafiek1").SeriesCollection(Range("J35").Value).MarkerForegroundColorIndex = 3
Charts("Grafiek1").SeriesCollection(Range("J35").Value).XValues = _
        Worksheets("Zeefanalyse").Range("H36:H41")

Charts("Grafiek1").Protect Password:="01FGSlab1962"
Sheets("Zeefanalyse").Unprotect Password:="01FGSlab1962"
Range("K11") = 1
Sheets("Zeefanalyse").Protect Password:="01FGSlab1962"
End If
Sheets("Grafiek1").Visible = True
Sheets("Grafiek1").Activate
End Sub

It seems to flag up when it gets to the

 Charts("Grafiek1").SeriesCollection(Range("I35").Value).Border.ColorIndex = 10
    Charts("Grafiek1").SeriesCollection(Range("I35").Value).MarkerBackgroundColorIndex = 10
    Charts("Grafiek1").SeriesCollection(Range("I35").Value).MarkerForegroundColorIndex = 10

Part of the code..

Any ideas?

 

[Rx_]

There were some difference between 2003, 2007, and 2010. I will list some general ones to get you started. You can apply this to looking more into the Excel Object Model.

In the Code Module, go to Tools - References - make sure that any references are not "missing".

The Object Model had a big change regarding Pivot Tables.

Highly suggest reading the following:
https://technet.microsoft.com/en-us/library/cc179167.aspx
Look at Compatibility mode, VBA upgrades and the Charting object.
Remember, there was a mode so if only half the office was updated.

Strongly suggest adding Error trapping to existing modules to help solve problems.

Since the code segment included Charts: "Chart.Add" give automation error in Excel 2007 when there are more than 1 cell selected.

Didn't see any error checking on the example, just to get started, this is a basic idea to consider. But, even the error trapping was sometimes necessary to be changed in Excel 2010.
On Error Resume Next
Intruction_Excel10_Could_be_buggy_1
if Err.Number <>0 Then
Err.Clear ' <= Error.Clear is really necessary
Intruction_Excel10_Could_be_buggy_2
if Err.Number <>0 Then

Range(...).Paste(xlFormulas) are much more likely to crash on a near name conflict.
There are other instance of code being touchy. I find myself using:
Application.displayalerts = False
'Range(MyRange).Paste(xlFormulas) ' or other touchy code in question
Application.displayalerts = True

Because the code example has both Protection and Charts, the following discussion would be worth the visit:
http://www.mrexcel.com/forum/genera...xcel-2003-excel-2010-compatibility-issue.html
This group actually sends issues to Microsoft Excel Development team. It would appear that the Protection and Excel Chart were .... difficult.