PDA

View Full Version : Excel 2007 After Password Entry Not Changing Permissions



CMSS
08-09-2010, 08:24 AM
I may have posted this question early last week, but I have not received a response and I can no longer find the original post, so I'm sorry if it is a re-post. If you have the url for the original, you can just post it here to answer my question. Otherwise:

I have a macro that runs when the workbook is open. It runs an inputbox that asks the user for a password, and then searches for the password entered on a veryhidden sheet at the end of the workbook. This sheet has a list of all the valid passwords, along with specific code-words adjacent to them that designate that password's access permissions. If the password entered by the user is invalid, an error message appears and they have another chance to enter a valid one. The user has three chances to enter a valid password before Excel closes. If the user enters a valid password, then the adjacent codeword is defined to a variable that enables certain permissions in the entire workbook. For example, if the user enters the valid password 'Bob', then the adjacent code-word 'User' allows the owner of the password 'Bob' to see certain sheets, or enter data into specific cells on specific sheets. My problem is that these permissions are not happening. When the user enters a valid password, the entire workbook remains locked and protected.

Here is my 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": With Sheets("Session Archive")
.Select
.Unprotect Password:="c"
Columns("P").Locked = False
.Protect Password:="c"
End With
Sheets("Calculator").Select
Case "Reviewer": With Sheets("Session Archive")
.Select
.Unprotect Password:="c"
Columns("N,O").Locked = False
.Protect Password:="c"
End With
With Sheets("Calculator")
.Select
.Unprotect Password:="c"
Range("D15:D21,D23:D28,D34,D35,E12,E13,H2,H29:H33").Locked = False
Range("H2").Select
.Protect Password:="c"
.EnableSelection = xlUnlockedCells
End With
Case "Finance": With Sheets("Calculator")
.Select
.Unprotect Password:="c"
Range("D7,D8,D10,D15:D21,D23:D28,D34,D35,E12,E13,H2,H29:H33").Locked = False
Range("H2").Select
.Protect Password:="c"
.EnableSelection = xlUnlockedCells
End With
Case "User": With Sheets("Calculator")
.Select
.Unprotect Password:="c"
Range("D15:D21,D23:D28,D34,D35,E12,E13,H2,H29:H33").Locked = False
Range("H2").Select
.Protect Password:="c"
.EnableSelection = xlUnlockedCells
End With
End Select
Sheets("Permissions").Visible = xlVeryHidden
Exit Sub
End If
Next i
Application.Quit