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]

Peter,

UNPROTECT, run your code, PROTECT.

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

 

[Goppi]

doesn't help.

 

[SkipVought]

Your For..Next code works for me!

What does not work?

Please explain!

Be Specific! Be Complete! Be Concise!

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

 

[Goppi]

Skip, I'm not quite sure what information else I could give you besides the one that I have already posted: If I try to delete the series I get the error message: Run-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.

This error message shouldn't happen - the chart is locked but the protection shouldn't only apply for the UserInterface, not for VBA code. If I leave the protection bit out it the code that deletes the series works fine.

 

[SkipVought]

[red]UNPROTECT[/red]...

BEFORE you run your code!!!!!!!!!!

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

 

[Goppi]

... the worksheet is unprotected before I run the code.

 

[SkipVought]

Please post ALL your code.

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

 

[Goppi]

Skip,

I created a complete new file with a chart just for testing purposes. I reduced the code as much as possible and could recreate the same behaviour. The code is as follows:

Sub xy()
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True
With ActiveSheet.ChartObjects(1).Chart
For Each x In .SeriesCollection
x.Delete
Next x
End With
End Sub

 

[SkipVought]

Why are you executing this code????

ActiveSheet.[red][b][u][i]Protect[/i][/u][/b][/red] Password:="x", UserInterfaceOnly:=True

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

 

[Goppi]

can you please specify what you mean with that question. I don't want to give a stupid answer like: to protect the sheet/to disable users to edit cells and charts.

 

[SkipVought]

UNPROTECT, run your code, PROTECT.

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

 

[Goppi]

Skip,

You just repeat yourself. It doesn't work your way.

 

[SkipVought]

Why can it not?

Please explain!

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

 

[Goppi]

If I would know that I would have solved the problem.

I tried the following test:
- The sheet is unprotected
- I run the sub xy1- which worked fine:
Sub xy1()
With ActiveSheet.ChartObjects(1).Chart
For Each x In .SeriesCollection
x.Delete
Next x
End With
End Sub
- I re-added some series
- I run the sub xy2 - but got the above mentioned error message in the line "x.Delete":
Sub xy()
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True
With ActiveSheet.ChartObjects(1).Chart
For Each x In .SeriesCollection
x.Delete
Next x
End With
End Sub

I don't need to protect the sheet after I run the sub because the sub protects it.

 

[SkipVought]

I should NOT need to hold your hand and spoon feed you.

Sub xy()
  With ActiveSheet.ChartObjects(1).Chart
    For Each x In .SeriesCollection
      x.Delete
    Next x
  End With[b][red]
'now AFTER YOUR DELETE CODE YOU PROTECT[/red][/b]
  ActiveSheet.EnableSelection = xlUnlockedCells
  ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True
End Sub

Was that so hard to figger out????

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

 

[Goppi]

Skip,

You are not as clever as you think. I would call your last message a workaround, but not the answer to the problem. Based on the docu VBA code should still be able to modify charts and cells when a sheet is protected with the option UserInterfaceOnly:=True. It works for Cells, but I couldn't get it working for Charts.

to make sure that it's not just a flaw in the actually excel file I created a tesat-file with the sub xy1 and xy2. The original excel file that I want to protect has instead of the xy2 sub 2 subs - the first sub protects the file and the second sub updates the chart (whereas it first of all deletes the whole series). the second sub has to run even if the sheet is protected. Because of this your workaround doesn't work.

 

[SkipVought]

Sub xy()
[b]  ActiveSheet.UnProtect Password:="x"[/b]
  With ActiveSheet.ChartObjects(1).Chart
    For Each x In .SeriesCollection
      x.Delete
    Next x
  End With
'now AFTER YOUR DELETE CODE YOU PROTECT
  ActiveSheet.EnableSelection = xlUnlockedCells
  ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True
End Sub

what's wrong with that?

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

 

[xlbo]

Skip - Goppi is technically correct - the "userinterfaceonly" argument of the protection method should mean that you can manipulate objects via code even though the sheet appears protected to the user.



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

 

[Goppi]

It's still a workaround but that would work. I'm still not sure if it's a bug or not. The MS docu says clearly:
When you use the Protect method with UserInterfaceOnly argument set to True (UserInterfaceOnly=True), you protect the user interface but not macros. If this argument is omitted, protection applies both to macros and to the user interface.