How do I lock several sheets

[Guest]

I have a spredsheet with 100 identical sheets, and I like to lock/protect all
the sheets. Is it possible to do this in a simple way instead of that I have
to lock each of them.

 

[Bernard Liengme]

This pair of subroutines with protect/Unprotect all the worksheets in the
current book. Unfamiliar with VBA? It really can be fun. See David
McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub LockDown()
For Each ws In Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

Sub UnlockAll()
For Each ws In Worksheets
ws.Unprotect
Next
End Sub

 

[Jane]

You're right: It can be fun - especially when it works!!

 

[Jane]

An additional question: How do I make sure, that the protection can't be
removed by the user og my spredsheet? I would like to incorporate a code in
my protection.

 

[Dave Peterson]

That's the bad news with excel. Worksheet protection is pretty easily broken.
If the user is dedicated, they'll find these newsgroups and get an answer in
moments.

But you can make it a little tougher by protecting your code:

Open your workbook
Open the VBE (alt-f11)
select your poject
Tools|VBAProject Properties|Protection tab
Give it a memorable password.

Save your workbook, close it and reopen it to test that protection.

Be aware that this password can be broken or avoided, too. So it's not
foolproof.