Can't delete series in protected chart

[Goppi]

I'm trying to delete a series via VBA before populating it again. I protected the sheet and with the option UserInterfaceOnly=True and the chart is set to locked. the protection of the cells works fine and I can still change their value via VBA, but it doesn't work for the chart. If I try to delete the series I get the error message Ron-time error 1004

The cell or chart you are trying to change is portected and therefore read-only. The debugger than jumps into the row with the delete.

Worksheets(1).EnableSelection = xlUnlockedCells
Worksheets(1).Protect Password:="x", UserInterfaceOnly:=True


With ActiveSheet.ChartObjects(1).Chart
For Each x In .SeriesCollection
x.Delete
Next x


any ideas?

Thanks
Peter

 

[SkipVought]

I've never trusted that feature.

It seem just as easy to me to remove the protection before dickerin with code.

I'm sorry that I overlooked this particular issue with you, Goppi.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Goppi]

No problem - It is easy to unprotect and protect it again, but if the code (which is in the original file much bigger than in the example above) for whatever reason would raise an error the worksheet would be completely unprotected and I can't have that...

 

[xlbo]

Skip - for the record - UserInterFaceOnly works just fine on worksheets / cells etc - main plus point is that you don't have to remember to keep setting / unsetting protection

There seems to be a seperate protection property for charts and I think they may belong to the drawingobjects collection which has a different protection state than the cells on a worksheet - I have a feeling that UIFO just doesn't work for charts...... :-(

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shetlandbob]

if the code (which is in the original file much bigger than in the example above) for whatever reason would raise an error the worksheet would be completely unprotected and I can't have that...

Use

sub mysub
OnError Goto capture_error
..
..
.. all my code
..
  Exit Sub
capture_error:
  ActiveSheet.EnableSelection = xlUnlockedCells
  ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True
End Sub

This should(?) capture all occurances of errors so that your sheet remains protected.

Robert Cumming

http://www.consultantengineer.co.uk

 

[Goppi]

It appears that MS knows about this problem, but only for the versions Excel95/97:

http://support.microsoft.com/default.aspx?scid=kb;en-us;140541