Protecting Worksheets

[Pistols14]

Is there any way to protect all the worksheets in a workbook without having
to go to each individual worksheet and using Tools > Protection > Protect
Worksheet?

 

[Freddy]

There should be an option in tools - protect to Protect the workbook as well
as to protect each individual sheet

 

[Pistols14]

I have tried using this but it does not seem to do what I want to do. I have
password protected sheets in a workbook so that others cannot change certain
data or formulae within the worksheets. When I use the Protect Workbook
function it allows any user to change the data or formulae even though I
password protected the workbook. If I protect the individual sheets though it
does not allow any other user to change the data or formulae that I do not
want changed.

 

[Dave Peterson]

Nothing built into excel.

But you could create a macro:

Option Explicit
Sub ProtectThemAll
dim myPwd as string
dim wks as worksheet
myPwd = "hi there"

for each wks in activeworkbook.worksheets
wks.protect password:=mypwd
next wks
end sub
Sub UnProtectThemAll
dim myPwd as string
dim wks as worksheet
myPwd = "hi there"

for each wks in activeworkbook.worksheets
wks.unprotect password:=mypwd
next wks
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[baldmosher]

Nothing built into excel.
But you could create a macro:

I've done this to good effect.

But if you do this, make sure you name your macro something not very
obvious, in case the recipient simply runs your "unlock" macro to unlock the
sheets!

Also you should password protect your macro project to prevent people
discovering your password within the code.

 

[Zone]

Just to add to what's already been said, you can input your own password
using a macro, like this. Of course, don't forget the password! James

Sub Prot()
Dim sht As Worksheet, pw As String
pw = InputBox("Password?", "Protect Worksheets")
For Each sht In ThisWorkbook.Worksheets
sht.Protect pw
Next sht
End Sub

Sub UnProt()
Dim sht As Worksheet, pw As String
pw = InputBox("Password?", "Unprotect Worksheets")
For Each sht In ThisWorkbook.Worksheets
sht.Unprotect pw
Next sht
End Sub

 

[Dave Peterson]

On top of these recommendations, the OP should be aware that worksheet
protection isn't made to protect intellectual property. It's made to help
prevent the user from making typing errors--like overwriting cells with
important formulas.

 

[Pistols14]

Thanks to everyone for that. It worked perfectly (and I now know a little bit
about macros also!).