Protect / unprotect worksheet through vba

shanzek

New Member
Joined
Feb 23, 2004
Messages
19
I am creating workbookA by taking a templateA and pasting in information from workbookB. The goal is to have the finished workbookA with some cells locked, and some unlocked. My code works fine until I tried to protect the worksheet in the template. Now it can't paste info into it, since it is locked.

I figure my two choices are:
(1) start with template unprotected, paste info in, then protect it
(2) start with template protected, unprotect it in vba, paste in info, protect it

Any suggestions as to the best approach? I'm not protecting it for security, just to restrict where people key in information.

Any examples would be really appreciated also.

Thanks
Steve
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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:

Code:
    sh.EnableSelection = xlUnlockedCells
    sh.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True

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
 
Upvote 0
Try putting the EnableSelection line of code from the recorder in Smitty's "ProtectAll" macro before the password line.

It worked for me...
 
Upvote 0
Kristy,
here is the code as I currently have it:
Code:
For Each sh In ActiveWorkbook.Worksheets
    sh.EnableSelection = xlUnlockedCells
    sh.Protect
Next sh

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
 
Upvote 0
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
 
Upvote 0
:pray: 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top