protect more than one sheet at a time

J

jbJB

How can I protect more than one sheet at a time. If I select all sheets in a
workbook, the dropdown menu will not allow me to protect all sheets at once.
Instead I have to manually protect each sheet which can be very
timecomsumming if I have over 50 sheets in a workbook.
 
J

Jim Thomlinson

The only way to do it is with code something like this...

Sub ProtectAll()
dim wks as worksheet

on error resume next
for each wks in worksheets
wks.Protect Password:="Tada"
next wks
end sub

Sub UnprotectAll()
dim wks as worksheet

on error resume next
for each wks in worksheets
wks.UnProtect Password:="Tada"
next wks
end sub
 
P

Paul B

jbJB, only with a macro, like this,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123" 'Change Password Here, use "" for no password

Next ws

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Top