How to auto protect worksheet and allow user to edit range

[fructine]

Hello,

I currently use a VBA to auto protect my worksheets upon closing the document. If users do not have a password, they can view the document but, they cannot edit.
I want to allow those who do not have the password to only be able to edit a range of cells. How should I write the code instead?

My current code is:
Private Sub Workbook_Open()
MsgBox ("REMINDER!! Please enter your LE02 estimates by the deadline of MARCH 10.")

Dim ws As Worksheet
Dim strPassWord As String
strPassWord = InputBox(Prompt:="Password", _
Title:="Enter Password", Default:="User Password")

If strPassWord = "mypassword" Then
Call UnlockSheet(Sheets("Sheet1"), strPassWord)
Call UnlockSheet(Sheets("Sheet2"), strPassWord)
Call UnlockSheet(Sheets("Sheet3"), strPassWord)
Else
Call LockSheet(Sheets("Sheet1"))
Call LockSheet(Sheets("Sheet2"))
Call LockSheet(Sheets("Sheet3"))
End If

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim strPassWord As String

strPassWord = "mypassword"

Call LockSheet(Sheets("Sheet1"))
Call LockSheet(Sheets("Sheet2"))
Call LockSheet(Sheets("Sheet3"))
End Sub
Private Sub LockSheet(sheet As Worksheet)
sheet.Protect Password:=strPassWord, DrawingObjects:=True, Contents:=True, Scenarios:=True
sheet.EnableSelection = xlNoSelection
End Sub
Private Sub UnlockSheet(sheet As Worksheet, strPassWord As String)
sheet.Unprotect Password:=strPassWord
End Sub

 

[fructine]

He's right about this code. It is asking for a password upon opening. Very similar to what I was building. Although, unless you have locked your VBA project with a password, anybody could go see the password. What would you like to do Fructine?

Jeff

I have tried both methods and neither worked. After I click on "unprotect sheet" in the "review" tab, the whole sheet gets unlocked and I can edit everything (protected and unprotected cells.

 

[jtakw]

I have tried both methods and neither worked. After I click on "unprotect sheet" in the "review" tab, the whole sheet gets unlocked and I can edit everything (protected and unprotected cells.

Obviously, that's how it works!

You can "Lock" a cell, make the formula "Hidden", but it doesn't take effect until the Sheet is Protected. If the sheet is unprotected, well, it's not protected.

 

[Jeffrey Mahoney]

I wasn't quite done creating code for you. I had stopped because it looked you had other code that was doing the same thing. Now, to try to parse out your issue

When a sheet gets protected using a password, Only those cells you unlocked can be edited. It sounded like you needed two levels of security. The first level was for anybody that opened the workbook. This first level only gave users permission to edit a few select cells. With the macro I was creating, a user could enter a password and it would unprotect the sheets, thus giving this person the ability to edit any of the cells.

When you unprotected the sheet, did it ask you for a password? If it didn't, we still have work to do. If it did, then it's doing its job.

Jeff

 

[jtakw]

OK,

Think of it this way:

Lock a cell = pushing the lock button of your car door
Protect Sheet = closing the car door

If you lock your car door, but leave the door open, anybody can get in. (locking a cell without protecting the sheet)
If you lock your car door, and close the door, nobody can get in. (locking a cell and protecting the sheet)

From what I understand, here's what you need to do.

First:
With the sheet(s) unprotected, lock all the cells you don't want unauthorized access to, meaning only people with the password can access them.

Second:
Make sure the cells you want anybody to be able to edit are not locked, meaning anybody, with or without the password can access them.

Third:
Copy and paste this code in the Workbook module, this is just a cleaned up version of the original code in your first post, I gather you found this code somewhere but don't really understand how it works. As it is now, it will NOT ask for any password when the workbook is opened, unlocked cells can be accessed by anyone, locked cells will ONLY be available to people with the Sheet password (in this case "mypassword"), you can change that within the code. If anyone with the password unprotects any or all of the sheets, and forgets to protect them back, the code will auto protect the sheets before the workbook closes and also when it opens:

Private Sub Workbook_Open()
    MsgBox ("REMINDER!! Please enter your LE02 estimates by the deadline of MARCH 10.")
    Call LockSheet(Sheets("Sheet1"))
    Call LockSheet(Sheets("Sheet2"))
    Call LockSheet(Sheets("Sheet3"))
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call LockSheet(Sheets("Sheet1"))
    Call LockSheet(Sheets("Sheet2"))
    Call LockSheet(Sheets("Sheet3"))
End Sub
Private Sub LockSheet(sheet As Worksheet)
    Const strPassWord As String = "mypassword"
    sheet.Protect Password:=strPassWord, DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Four:
Within the VBE, you should be here already since you just put in the code above, click "Tools" on the top menu, then click "VBAProject Properties", in the pop up window, click "Protection" tab, make sure "Lock Project for viewing" is checked, input password and confirm, click OK. (make sure you write down your password for future reference, you'll need it to edit any of the macros)

Five:
Save and close the workbook, reopen the workbook, test if it's doing what you want.