Protecting / Unprotecting multiple worksheets

[Phylis Sophical]

Is there a way to protect and/or unprotect an entire group of worksheets? I have 25 in one workbook. Protect Workbook does not do what I want it to, unless I've missed something. I want only certain cells accessable to the users. But occasionally I have to unprotect them all to change something.

 

[Legacy 98055]

Sub UnprotectAllSheets()
Dim s
For Each s In Worksheets
s.Unprotect password:="YourPassword"
Next s
End Sub

Sub ProtectAllSheets()
Dim s
For Each s In Worksheets
s.Protect password:="YourPassword"
Next s
End Sub

The password would need to be the same throughout...
Hope this helps,
Tom

 

[Phylis Sophical]

Pardon my 'newbieness', but is that a Macro? I don't want to use a password. Do I need the password part?

I was afraid it would be something complex like this, but if it is a Macro, well, I guess I now learn how to use them. At least I have the formula.

 

[Legacy 98055]

No need for a password...

Sub UnprotectAllSheets()
Dim s
For Each s In Worksheets
s.Unprotect
Next s
End Sub

Sub ProtectAllSheets()
Dim s
For Each s In Worksheets
s.Protect
Next s
End Sub

This macro is nothing more than a repetitive action you would need to perform on each sheet.

Tom

 

[Phylis Sophical]

Thanks Tom. I was afraid it was going to be the 'M' word. I'm leafing through my Excel How To book as we speak. Will get back to you with the results. Thxs again.

 

[Phylis Sophical]

Most of you will probably cringe when you see how I accomplished this, but the end justifies the means I guess.

I assume that code goes into some visual basics editor or something and I haven't a clue how to navigate that. Opened Visual Basics Editor but couldn't figure out how to get passed the blank screen. (no sheet of paper to work on).

So I recorded a macro, which turned out to be very simple to do, except it wouldn't work if I added worksheets or changed the name on the tab. So I clicked on edit, deleted the code (if that's the proper word) and pasted the code you wrote, Tom. That worked.

If there is an easier way to accomplish this that doesn't task your time too much, feel free to enlighten me. I do have one specific question though. Is the code in a Macro case-sensitive? Feel free to correct me if I'm using the wrong terms.