PDA

View Full Version : Excel 2007 Unlocking Specific Cells Based on Password



CMSS
08-04-2010, 01:13 PM
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:



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

Simon Lloyd
08-04-2010, 01:18 PM
Try this in the ThisWorkbook modulePrivate Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Calculator").Unprotect Password:="c"
Range("A1:XFD65536").Locked = True
Sheets("Calculator").Protect Password:="c"
Sheets("Permissions").Visible = True
End Sub

CMSS
08-04-2010, 01:24 PM
Even with that code, when I run the macro, no matter what user name I enter, they cannot select anything on the worksheet.

p45cal
08-04-2010, 01:32 PM
cross post:http://www.mrexcel.com/forum/showthread.php?t=486109

anywhere else?


see:http://vbaexpress.com/forum/showthread.php?t=6905

Simon Lloyd
08-04-2010, 01:40 PM
pa5cal, thanks for pointing that out, CMSS Please read this (http://www.excelguru.ca/node/7) before cross posting!