Excel 2007 Unlocking Specific Cells Based on Password

CMSS

New Member
Joined
Jul 29, 2010
Messages
18
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,615
Messages
5,832,713
Members
430,156
Latest member
Amosjack1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top