Protect multiple worksheets in a workbook at one time in EXCEL.

[Guest]

I'm going to give an example of what I have to hopfully give a better
understanding of what I am trying to accomplish.

I have a workbook with 40 worksheets. 30 of these work sheets need to have
the same specific cells unprotected, while the rest of the sheets/cells need
to be protected. I have tried to only unlock the needed cells under cell
format, and then protecting the entire workbook, but this did not provide the
results I need. I found that the only way I am able to achieve what I am
trying to accomplish is to protect each sheet individually, one at a time.
This is very time consuming, protecting 30 sheets in a workbook.

What would make things easier is selecting multiple sheets and allowing all
sheets to be protected at once.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...7aa0852&dg=microsoft.public.excel.programming

 

[Gary Keramidas]

try these in a module

Option Explicit
Dim ws As Worksheet
Sub Unprotect_All()
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Unprotect
Next ws
Application.ScreenUpdating = True
End Sub


Sub Protect_All()
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Protect
Next ws
Application.ScreenUpdating = True
End Sub

--


Gary


"Daniel N. (McKinney, TX)" <Daniel N. (McKinney,
TX)@discussions.microsoft.com> wrote in message
news[email protected]...

 

[Guest]

Daniel
There is a difference between protecting and "Locking."
Cells have a locked property that is either true or false.
After this property is set for each cell in a sheet, the Sheet can
then be Protected. Only when a sheet is protected does the user
see the effect of a cell which has its Locked property set to true.

It sounds like what you want to do is:
When your sheets are UNprotected, you select all cells (Ctrl+A) and then
Lock them
Format >Cells>Protection> Check the Locked box >OK
Then select the range of cells that you want to remain unlocked for your
end user.
Again click on
Format >Cells>Protection>Locked (to UNcheck the Locked box)
This will leave these cell Unlocked WHEN you protect that worksheet.

Do the above for all your sheets.

Now you can PROTECT all your sheets with the following code, leaving the
user with data entry access to just the UNLocked cells, and viewing access to
all cells.

In a module:

Option Explicit

Sub ProtectAllSheets()

Dim Sh as Worksheet
For each Sh in ActiveWorkbook.Worksheets
Sh.Protect Password:="MyPassword"
Next Sh
end Sub

You can also protect the Workbook now to keep the user
from inserting/copying/moving/deleting sheets etc.
But keep in mind that these "Protection" processes are only
good for the casual user.
Most people using these forums know how to remove
protection from Excel in a matter of seconds.