Excel 2007 Unlocking Specific Cells Based on Password


New Member
Jul 29, 2010
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").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)
    If IsError(Application.Match(name, Range("A2:A65536"), 0)) Then
        MsgBox ("Invalid Name Entered!")
      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
                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
             Sheets("Calculator").Protect Password:="c"
             Sheets("Calculator").EnableSelection = xlUnlockedCells
        End Select
        Sheets("Permissions").Visible = xlVeryHidden
      Exit Sub
    End If
  Next i

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...