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

 

[Jeffrey Mahoney]

You should change the format of the cells you want them to edit to unlocked. Home-Format-Format Cells.

 

[fructine]

I have unlocked the cells and saved. But after I close the document and re-open, unless I enter the password, it wont let me edit the unlocked cells.

 

[Jeffrey Mahoney]

The password is protecting the workbook. You will need to remove that password. Password protect each sheet.

 

[jtakw]

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

Hi,

Try commenting out the above in red.

 

[fructine]

I have password protected each sheet, but this document will be used by several other people, and upon closing if they do not password protect each sheet, the document remains unprotected.
What I am trying to do is to allow people to edit cell ranges and for the document to auto protect upon closing.

 

[fructine]

Commenting out? Please explain.

 

[jtakw]

Commenting out? Please explain.

Just look at my post, the line in red, I added ' in front, which disables that line, or you can just delete that line. Test with a copy of your file.
This line makes your worksheets READ ONLY, and cannot be edited in any way.

 

[fructine]

Just look at my post, the line in red, I added ' in front, which disables that line, or you can just delete that line. Test with a copy of your file.
This line makes your worksheets READ ONLY, and cannot be edited in any way.

I have taken the line out and still get the same result. Unless I enter the password upon opening the document, I cannot edit anything.

 

[Jeffrey Mahoney]

Fructine,

If you password protect an entire workbook, forcing users to enter a password before opening the workbook, you won't be able to achieve your goal. Meaning, nobody can edit a workbook unless they enter the correct password. The only way for you to allow limited users to edit cells and authorized users to edit all the cells is to:
A) Remove the workbook password
B) Unprotect the cells you want the limited users to edit
C) Protect all worksheets