Lock Spreadsheet with Password.

[JoeSalmi]

Can someone write a macro that locks all my cells with a formula in them so my users can't keep destroying my worksheets? I need it to work on ALL worksheets in the Spreadsheet but have a password that allows me to adjust or unlock the entire spreadsheet???

 

[repairman615]

Hello,

Maybe instead of the activesheet.protect, use something like activeworkbook.protect...I just do not know how

Here is what I got, maybe you can use a bit to build one that works for you.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LockFormulas()<br><br><SPAN style="color:#00007F">Dim</SPAN> sh<br>    <br><SPAN style="color:#00007F">For</SPAN> sh = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Sheets.Count<br>    Sheets(sh).Activate<br>    <br>    ActiveSheet.Unprotect<br>    Cells.SpecialCells(xlCellTypeFormulas).Locked = <SPAN style="color:#00007F">True</SPAN><br>    ActiveSheet.Protect<br>    <br><SPAN style="color:#00007F">Next</SPAN> sh<br><br>Sheets(1).Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[y3kesprit]

Can someone write a macro that locks all my cells with a formula in them so my users can't keep destroying my worksheets? I need it to work on ALL worksheets in the Spreadsheet but have a password that allows me to adjust or unlock the entire spreadsheet???

Couldn't you just use the simple password protect function to lock the spreadsheet or the workbook?

 

[repairman615]

This may be closer:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LockFormulas()<br><br><SPAN style="color:#00007F">Dim</SPAN> ws<br>ActiveWorkbook.Unprotect ("Password")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>    ws.Activate<br>    <br>    <SPAN style="color:#007F00">'ActiveSheet.Unprotect</SPAN><br>    Cells.SpecialCells(xlCellTypeFormulas).Locked = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#007F00">'ActiveSheet.Protect</SPAN><br>    <br><SPAN style="color:#00007F">Next</SPAN> ws<br>ActiveWorkbook.Protect ("Password")<br>Sheets(1).Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Firecop1348]

I use this to lock my sheets the DEFALT password is "haleyjo" but you can change it to whatever you want.

Sub Protect1()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Protect Password:="haleyjo"
Next WS
ActiveWorkbook.Save

End Sub

to Unprotect the sheet I use

Sub unprotect()
Dim WS As Worksheet
here:
Opensezme = InputBox("Please enter the password.", _
"Accessing...")
If Opensezme = "" Then
Exit Sub
End If
If Opensezme = "haleyjo" Then
For Each WS In ThisWorkbook.Worksheets
WS.unprotect Password:="haleyjo"
Next WS
Else
TryAgain = MsgBox("Invalid password entered...", _
vbYesNo, "Try Again")
Select Case TryAgain
Case Is = vbYes
GoTo here

hope this helps

 

[JoeSalmi]

This may be closer:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LockFormulas()<br><br><SPAN style="color:#00007F">Dim</SPAN> ws<br>ActiveWorkbook.Unprotect ("Password")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>****ws.Activate<br>****<br>****<SPAN style="color:#007F00">'ActiveSheet.Unprotect</SPAN><br>****Cells.SpecialCells(xlCellTypeFormulas).Locked = <SPAN style="color:#00007F">True</SPAN><br>****<SPAN style="color:#007F00">'ActiveSheet.Protect</SPAN><br>****<br><SPAN style="color:#00007F">Next</SPAN> ws<br>ActiveWorkbook.Protect ("Password")<br>Sheets(1).Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This gives me a Runtime Error Saying no Cells were found???

 

[JoeSalmi]

I use this to lock my sheets the DEFALT password is "haleyjo" but you can change it to whatever you want.

Sub Protect1()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Protect Password:="haleyjo"
Next WS
ActiveWorkbook.Save

End Sub

to Unprotect the sheet I use

Sub unprotect()
Dim WS As Worksheet
here:
Opensezme = InputBox("Please enter the password.", _
"Accessing...")
If Opensezme = "" Then
Exit Sub
End If
If Opensezme = "haleyjo" Then
For Each WS In ThisWorkbook.Worksheets
WS.unprotect Password:="haleyjo"
Next WS
Else
TryAgain = MsgBox("Invalid password entered...", _
vbYesNo, "Try Again")
Select Case TryAgain
Case Is = vbYes
GoTo here

hope this helps

This one protects the ENTIRE Spreadsheet which doesn't allow my users to enter anything in any of the fields.

 

[Smitty]

This one protects the ENTIRE Spreadsheet which doesn't allow my users to enter anything in any of the fields.

You need to go into each data entry sheet and unlock the data input cells first. By default all cells on a sheet are locked, so if you don't unlock them first you can't interact with anything.

HTH,

 

[JoeSalmi]

You need to go into each data entry sheet and unlock the data input cells first. By default all cells on a sheet are locked, so if you don't unlock them first you can't interact with anything.

HTH,

I've never worked with locking cells before so please excuse my ignorance. I will give it a go. Thanks

 

[Smitty]

No worries, just select the cells where you want to allow entry (you can select multiple, non-contiguous cells with Ctrl+Left-Click), then goto Format-->Cells (Ctrl+1)-->Protection-->Uncheck "Locked". Now when you protect the sheet those cells can accept entries.