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]

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

That's unfortunate. But thank you.

 

[Jeffrey Mahoney]

You can still auto protect worksheets when a user closes the workbook. I would create a Setup Sheet that has a list of worksheets you wanted protected, unless you wanted them all protected. You can protect your VBA code easily. Just store a password in a protected place on the Setup Sheet. The Authorized users can run a macro that asks them for a password and then it simply unprotects all the sheets.

I could help you with that

Jeff

 

[fructine]

Yes it would be great if you could help me out with that. I am still new at VBA.

 

[Jeffrey Mahoney]

Ok,

You will need to remove workbook password. You'll need to identify the cells you want the limited users to edit and unprotect those cells. You'll need to make sure that all the rest of the cells are protected. You'll need to create a sheet called Setup. I am going to give you some code that will hide the Setup Sheet so that end users cannot see it. All these steps will make sense once we complete the task. On the Setup Sheet go to Cell JM999. Enter the password you want the authorized users to enter to unprotect all the sheets. Make the text in that cell white so that you can't see what's in the cell until you put your cursor on it. Don't tell me the password. You will need to Password protect your VBA code.

If any of these steps are foreign to you, please ask and I'll help.

Next, some code.

Jeff

 

[Jeffrey Mahoney]

The code below needs to go into a regular Module. The first two subs will only be run by you, the administrator. Those two SUBS won't be visible to regular users because you are going to protect your VBA code with a password. XlSheetVeryHidden makes a sheet completly hidden to everyone until you use the other SUB to unhide it. The UnprotectSheets macro can be run by the users. It asks for a password and gives them multiple chances to get it right. It compares the password they enter to the password you put on the Setup Sheet.

Next will be the code that Protects the sheets when a user closes the workbook.

Private Sub HideSetup()
  Dim Sht As Worksheet
  
  Set Sht = Sheets("Setup")
  Sht.Visible = xlSheetVeryHidden
  
End Sub

Private Sub UnHideSetup()
  Dim Sht As Worksheet
  
  Set Sht = Sheets("Setup")
  Sht.Visible = xlSheetVisible
  
End Sub

Sub UnprotectSheets()
  Dim Sht As Worksheet
  Dim PW As String
  Dim V As Variant
  Dim iPW As String
  Dim Tries As Integer
  Dim MaxTries As Integer
  
  MaxTries = 4
  
  PW = Sheets("Setup").Range("JM999").Value
  Tries = Sheets("Setup").Range("JM1000").Value       'Get the number of tries from this session
  
TryAgain:
  V = InputBox("Please Enter the password", "Enter the Password")
  iPW = V
  If iPW <> PW Then
    Tries = Tries + 1                         'Increment the number of tries
    Sheets("Setup").Range("JM1000") = Tries   'Store it on the Setup Sheet
    If Tries >= MaxTries Then                                'We have exceeded the maximum number of tries
      MsgBox "You have exceeded the number tries, goodbye"
      ThisWorkbook.Close SaveChanges:=False                 'Close the workbook without saving
                                                            'This is extreme. If you don't want this we could
                                                            'change the code to have a hand reach out and slap them
                                                            ':~)
    Else
      MsgBox "Incorrect Password, Please Try Again"         'Give them one more try
      GoTo TryAgain
    End If
  Else
    Sheets("Setup").Range("JM1000") = 0               'Reset the number of tries
    For Each Sht In ThisWorkbook.Worksheets           'Unprotect all the worksheets
      On Error Resume Next
      If Sht.Name <> "Setup" Then
        Sht.Unprotect (PW)
      End If
    Next Sht
    Sheets("Setup").Range("JM1001") = "UnProtected"  'Let the macro know to protect the sheets again
    MsgBox "Thank you"                        ':)
  End If
    
  
  
End Sub

 

[Jeffrey Mahoney]

BTW, You shouldn't use the same password to protect the VBA project as you are using to protect the sheets.

 

[jtakw]

The workbook was NOT password protected.
The reason the workbook is asking for password upon opening is all built within the OP's code, I've commented out all the unnecessary lines, replace your code with this version below and you're good to go, no need to do anything else, unless you want to.

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)
 Const strPassWord As String = "mypassword"
 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

The above is essentially the original code posted by OP in post #1, the part in Red requires the user to input password before proceeding.

 

[fructine]

Ok, all done. Please continue with code.
Thanks

 

[Jeffrey Mahoney]

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

The workbook was NOT password protected.
The reason the workbook is asking for password upon opening is all built within the OP's code, I've commented out all the unnecessary lines, replace your code with this version below and you're good to go, no need to do anything else, unless you want to.

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)
 Const strPassWord As String = "mypassword"
 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

The above is essentially the original code posted by OP in post #1, the part in Red requires the user to input password before proceeding.

 

[palermonz]

Does this work on Excel for Mac?