Protect ALL sheets in one coded macro

C

Corey

How can i word a code to protect ALL sheets in a workbook in a single step,
rather than individually?

Sub protect()
' Macro recorded 24-11-2006 by Corey
'
ThisWorkbook.Worksheets("Sheet 10").protect Password:="1234" ' <=== Some
thing like this workbook.worksheets(all).protect password:="1234"
ActiveSheet.protect
End Sub


How can i write this?

Corey....
 
P

Paul B

Corey,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="1234"

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
 
J

Jim Thomlinson

Give these a try...

Sub ProtectAll
dim wks as worksheet

for each wks in worksheets
wks.protect Password:="1234"
next wks
end sub

Sub UnProtectAll
dim wks as worksheet

for each wks in worksheets
wks.unprotect Password:="1234"
next wks
end sub
 
J

Jim Jackson

I have not tried this but it might work:

Sheets(Array("Sheet1","Sheet2", name each sheet in this manner)).protect
Password:="1234"

Else

x=1
for each sheet in workbooks
if x > sheets.count then exit for
end if
Sheets(x).protect Password:="1234"
next x
 
C

Corey

thanks Paul.

I have placed a command button on sheet1 to call the code you posted, but it
seem to ONLY apply to the sheet1.
Although the code refers to all sheets?
Why would this be?
Corey....
 
C

Corey

never mind.
worked it out.
Had cells selected as locked cells can be selected in some sheets.

Corey....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top