Protect / unprotect worksheet through vba

Start the macro recorder, do your stuff, then look at the code it generates

Here you go:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ProtectAll()
    <SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> myPassword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    myPassword = "password"
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        sh.Protect Password:=myPassword
    <SPAN style="color:#00007F">Next</SPAN> sh
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> UnrotectAll()
    <SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> myPassword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    myPassword = "password"
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        sh.Unprotect Password:=myPassword
    <SPAN style="color:#00007F">Next</SPAN> sh
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

Kristy and Smitty, thanks for the quick replies...

I've made progress, but have one 'minor' problem.

I've added the code from Smitty, which works fine for unprotecting the template so I can paste, and then protects it. The problem is that when it protects it, the parameters are such that you can select locked and unlocked cells.

I've used the macro recorder, as Kristy suggested, and it shows this code:


which, according to the help, should result in users only being able to select unlocked cells. But when I go into the resulting worksheet, it is 'protected', but the user can select locked cells. THey can't change them, as a message pops up, but I don't want them to be able to go into those cells.

Any thing else I need to try?
Thanks
Steve

Try putting the EnableSelection line of code from the recorder in Smitty's "ProtectAll" macro before the password line.

It worked for me...

Kristy,
here is the code as I currently have it:

I have no password currently assigned to the protection, so the 'sh.protect' line has no password parameter. I attempted to put the EnableSelection parameter on the protect line, but Excel didn't like that.

So I'm a little lost with your last instruction; I don't want to put the command outside the 'for each' loop, and it's already in front of the line that had the password and protect method.

Sorry I'm so dense...

Steve

Steve,

What you've done is correct. The problem is that the EnableSelection property will only remain in place as long as the workbook is open. As soon as you close it and re-open it you'll be able to select any cells again. I don't know why Microsoft did this but I'm sure they have some inane reason. Anyway, one possible solution is to put your code in the Workbook_Open event procedure. See the Workbook Modules section from my site if you're unsure how to do this (sorry that the page looks a bit crappy but I'm in the middle of renovating!).

HTH
Dan

ray: Dan, you (along with Kristy and Smitty) saved my bacon. I put the code into the workbook open procedure and it works fine (after I swapped the order - had to put the protect first, then the enable selection...
Thanks everyone. This site is super.
Steve

Hello:

I needed some help, I am trying to create a macro to protect sheets within the workbook file. below code is what I used but it is not working. I would keep this file open then go into each seperate workbook file to protect all the sheets in the file. Also I added a ctrl a to protect and ctl key r to unprotect the file. key Any help would be appreciated:

Sub AA___Protect_All_FY14_Calc_Sheets()
'AProtect All Calc Sheets Macro
'FY13 password is "test", macro must be updated to include "FY13" in array to function
Dim ws As Worksheet
Set WSArray = Sheets(Array("Census", "Emp_Data_7_1_13"))
Set WSArray = Sheets(Array("Calc Sheet (1)", "Calc Sheet (2)", "Calc Sheet (3)", "Calc Sheet (4)", "Calc Sheet (5)", "Calc Sheet (6)", "Calc Sheet (7)", "Calc Sheet (8)", "Calc Sheet (9)", "Calc Sheet (10)", "Calc Sheet (11)", "Calc Sheet (12)", "Calc Sheet (13)", "Calc Sheet (14)", "Calc Sheet (15)", "Calc Sheet (16)", "Calc Sheet (17)", "Calc Sheet (18)", "Calc Sheet (19)", "Calc Sheet (20)", "Calc Sheet (21)", "Calc Sheet (22)", "Calc Sheet (23)", "Calc Sheet (24)", "Calc Sheet (25)", "Calc Sheet (26)", "Calc Sheet (27)", "Calc Sheet (28)", "Calc Sheet (29)", "Calc Sheet (30)", "Calc Sheet (31)", "Calc Sheet (32)", "Calc Sheet (33)", "Calc Sheet (34)", "Calc Sheet (35)", "Calc Sheet (36)", "Calc Sheet (37)", "Calc Sheet (38)", "Calc Sheet (39)", "Calc Sheet (40)", "Calc Sheet (41)"))
Set WSArray = Sheets(Array("Calc Sheet (42)", "Calc Sheet (43)", "Calc Sheet (44)", "Calc Sheet (45)", "Calc Sheet (46)", "Calc Sheet (47)", "Calc Sheet (48)", "Calc Sheet (49)", "Calc Sheet (50)", "Calc Sheet (51)", "Calc Sheet (52)", "Calc Sheet (53)", "Calc Sheet (54)", "Calc Sheet (55)", "Calc Sheet (56)", "Calc Sheet (57)", "Calc Sheet (58)", "Calc Sheet (59)", "Calc Sheet (60)", "Calc Sheet (61)", "Calc Sheet (62)", "Calc Sheet (63)", "Calc Sheet (64)"))
For Each ws In WSArray
ws.Protect Password:="test1", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws
End Sub
Sub AA___UnProtect_All_FY14_Calc_Sheet()
'UnProtect All Budget Sheets Macro
Dim ws As Worksheet
'Set WSArray = Sheets(Array("census", "Emp_Data_7_1_13"))
Set WSArray = Sheets(Array("Census", "Emp_Data_7_1_13"))
Set WSArray = Sheets(Array("Calc Sheet (1)", "Calc Sheet (2)", "Calc Sheet (3)", "Calc Sheet (4)", "Calc Sheet (5)", "Calc Sheet (6)", "Calc Sheet (7)", "Calc Sheet (8)", "Calc Sheet (9)", "Calc Sheet (10)", "Calc Sheet (11)", "Calc Sheet (12)", "Calc Sheet (13)", "Calc Sheet (14)", "Calc Sheet (15)", "Calc Sheet (16)", "Calc Sheet (17)", "Calc Sheet (18)", "Calc Sheet (19)", "Calc Sheet (20)", "Calc Sheet (21)", "Calc Sheet (22)", "Calc Sheet (23)", "Calc Sheet (24)", "Calc Sheet (25)", "Calc Sheet (26)", "Calc Sheet (27)", "Calc Sheet (28)", "Calc Sheet (29)", "Calc Sheet (30)", "Calc Sheet (31)", "Calc Sheet (32)", "Calc Sheet (33)", "Calc Sheet (34)", "Calc Sheet (35)", "Calc Sheet (36)", "Calc Sheet (37)", "Calc Sheet (38)", "Calc Sheet (39)", "Calc Sheet (40)", "Calc Sheet (41)"))
Set WSArray = Sheets(Array("Calc Sheet (42)", "Calc Sheet (43)", "Calc Sheet (44)", "Calc Sheet (45)", "Calc Sheet (46)", "Calc Sheet (47)", "Calc Sheet (48)", "Calc Sheet (49)", "Calc Sheet (50)", "Calc Sheet (51)", "Calc Sheet (52)", "Calc Sheet (53)", "Calc Sheet (54)", "Calc Sheet (55)", "Calc Sheet (56)", "Calc Sheet (57)", "Calc Sheet (58)", "Calc Sheet (59)", "Calc Sheet (60)", "Calc Sheet (61)", "Calc Sheet (62)", "Calc Sheet (63)", "Calc Sheet (64)"))
For Each ws In WSArray
ws.Unprotect Password:="test1"
Next ws
End Sub