Error Message that I cant figure out

[spurs]

When I run the following code in Excel 2003, I do not get an error. When I run it in excel 2007, i get an error :

Run time error 1004:
A formula in the worksheet contains one or more invalid references
Verify that your forumulas contain a valid path, workbook, range name, and cell reference

on the following line

.Formula = "=SERIES(,Data!R1C1:R" & Nreadings & "C1,Data!R1C4:R" &_ Nreadings & "C4,1)"

in the code below

Sheets("TTT Ecc Rem").Unprotect
With Sheets("TTT Ecc Rem").ChartObjects("Chart 1").Chart
.SetSourceData Source:=Sheets("Data").Range("A1:A" & Nreadings),_ PlotBy:=xlColumns
With .SeriesCollection(1)
.Formula = "=SERIES(,Data!R1C1:R" & Nreadings & "C1,Data!R1C4:R" &_ Nreadings & "C4,1)"
.Name = "Ecc Removed"
.Border.ColorIndex = 11
.Border.Weight = xlThin
.Border.LineStyle = xlContinuous
End With
End With

the usual way is to run the code password protected (ie the vba code is password protected) Of course when this happens you cannot see exactly which line of code failed. So when I unprotect the code then run the macro, it shows me the line indicated above as the failed line. The odd thing is that after the error comes up, I can simply press F8 to run a single line of code, and it runs without error and everything looks fine like in excel 2003.

Some notes: the worksheet "Data" is not protected but it is hidden.

Anyone know what causes this?

 

[spurs]

I have discovered that the error seems to be caused by something relating to unprotecting the worksheet "TTT Ecc Rem"

If before I run the macro, I manually unprotect the worksheet "TTT Ecc Rem", the code runs fine. For some reason, unprotecting with the line
Sheets("TTT Ecc Rem").Unprotect

is working inconsistantly.

I have paid very close attention to making sure that the "TTT Ecc Rem" is correctly spelled, but as I said before, this workbook worked fine in excel 2003. Its just crashing in 2010.

Has anyone seen this type of behavioiur before; and if so, is there a way to improve performance.

 

[JoeMo]

Not sure this will help, but try changing the unprotect line to this

Sheets("TTT Ecc Rem").Protect UserInterfaceOnly:=True

 

[spurs]

Am I able to use this additonal code with a passowrd?

Also, now that you raised this issue, I found in the excel 2010 vba help a new syntax called chart.protect

is it possible that since the worksheet is a chart, it is unstable in 2010 because of this? If so, what syntax would work in both 2003 & 2010

 

[JoeMo]

Am I able to use this additonal code with a passowrd?

Also, now that you raised this issue, I found in the excel 2010 vba help a new syntax called chart.protect

is it possible that since the worksheet is a chart, it is unstable in 2010 because of this? If so, what syntax would work in both 2003 & 2010

Yes you can add a password to the statement. Not sure what you mean by "Worksheet is a chart". From the code it appears that the chart is an embedded chart in a worksheet. If its a standalone chart then the sheet is a chart sheet not a worksheet. In any case, I don't think this should cause XL2010 to behave differently than XL2003.

 

[spurs]

It is a chart embeddedin a worksheet

 

[spurs]

I have tried as suggested
Sheets("TTT Ecc Rem").Protect UserInterfaceOnly:=True

and the performance problem is identical

again, the problem is only happening in 2010 not in 2003

 

[spurs]

I got around the problem by putting
On Error Resume Nest

near the top of the code - but I really dont like doing that - but it works
go figure

is this a glitch in 2010?

 

[spurs]

Ok I was mistaken - see new post