Excel 2007 Unlocking Specific Cells Based on Password
I am having a problem with my code to allow different users access to certain cells. When the workbook opens, the user enters their name into an inputbox, and the code grants them access to certain cells based on their level of permission. However, when they change the worksheet and save, their permissions are saved with it. If another user signs in to the workbook, they are limited to or allowed access to the cells allowed to the previous user, regardless of the new user's permission parameters.
Here is my code:
Code:
Dim name As String
Dim permission As String
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Calculator").Select
Sheets("Calculator").Unprotect Password:="c"
Range("A1:XFD65536").Locked = True
Sheets("Calculator").Protect Password:="c"
Sheets("Permissions").Visible = True
For i = 1 To 3
name = Application.InputBox("Please Enter Name", "Login", "Enter Name Here", Type:=2)
Sheets("Permissions").Select
If IsError(Application.Match(name, Range("A2:A65536"), 0)) Then
Sheets("Calculator").Select
MsgBox ("Invalid Name Entered!")
Else
permission = Range("A2:A65536").Find(name, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1)
Select Case permission
Case "Admin": Sheets("Calculator").Select
Case "Finance": Sheets("Calculator").Select
Sheets("Calculator").Unprotect Password:="c"
Range("D7,D8,D10,D15:D21,D23:D28,D34,D35,E12,E13,H2,H29:H33").Locked = False
Range("H2").Select
Sheets("Calculator").Protect Password:="c"
Sheets("Calculator").EnableSelection = xlUnlockedCells
Case "User": Sheets("Calculator").Select
Sheets("Calculator").Unprotect Password:="c"
Range("D15:D21,D23:D28,D34,D35,E12,E13,H2,H29:H33").Locked = False
Range("H2").Select
Sheets("Calculator").Protect Password:="c"
Sheets("Calculator").EnableSelection = xlUnlockedCells
End Select
Sheets("Permissions").Visible = xlVeryHidden
Exit Sub
End If
Next i
Application.Quit
