Unprotect does not always work properly

[NicolaasH]

Hi,

I am having some difficulty with unprotecting worksheets. I have a procedure that should unprotect all worksheets in the workbook (about 10 sheets), but sometimes not all worksheets are then unprotected, it either skips sheets, or stops at a certain sheet. I'm not sure what is different in the situation when it does not work from situations when it does work. The workbook is activated when the procedure runs.

Sub ProtectionOff()

For Each WS In ThisWorkbook.Worksheets
    WS.Unprotect Password:="Pass"
Next WS

End Sub

Does anybody have any clue how to solve this?
Thanks for your help!
Nick

 

[xlbo]

nothing wrong with the code per se. Do you have any chart sheets in the workbooks ?

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

 

[NicolaasH]

Yes, there are several chart sheets. They are however at the back and do not appear to cause the trouble, although I cannot be sure.

 

[NicolaasH]

Maybe I should mention that there are several sheets with Lists (ListObject) and pivot tables. I have a suspision that the Lists and worksheet activation might cause some trouble. If I add a line
WS.Activate
I think the problem is gone. However, I do not really like this, since leafing through all de worksheets makes the code slower and it moves the focus away from the worksheet that is being worked on.

 

[PHV]

What about this ?

Sub ProtectionOff()
Set curWS = ThisWorkbook.ActiveSheet
Application.ScreenUpdating = False
For Each WS In ThisWorkbook.Worksheets
    WS.Activate
    WS.Unprotect Password:="Pass"
Next WS
curWS.Activate
Application.ScreenUpdating = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NicolaasH]

Hmmm, that looks guite good. I'll give it a try, thanks!