CODEWITHSUNDEEP
vbaexcelpasswordspassword-protection
Ben Smith
Ben Smith

Reputation: 819

Excel: How can I give different people various levels of protection and access

I have an excel spreadsheet that 3 different types of people can access, I want these 3 types of people to have different access rights. I want there to be people with full access, so can change the workbook, change the cells, change the contents and formats of the cells, etc. there are people with some access, so can only change some cells and can go on any of the sheets that are visible when the spreadsheet is access, and I want there to be people with limited access so can only go on certain sheets, change cells that are necessary to retrieve the data that is associated to that value, and cannot save the file.

Is there a way of doing this?

What I would really like is for when the file opens to ask the user for a password, and, dependent on what the password is, different cells are locked and different levels of access is applied.

Upvotes: 0

Views: 2471

Answers (1)

Dirk Horsten
Dirk Horsten

Reputation: 3845

First assign named ranges to the areas you want to protect, using a suffix indicating what to do with them. You can do that by selecting the range and typing a name that is not a valid Cell address left of the formula bar.

enter image description here

I created ranges named Someting_4U, which You and I can edit and Something_4Me, which only I can edit

Now copy the below code in the main ThisWorkbook module

enter image description here

Private Sub Workbook_Open()
    Dim Sht As Worksheet, Nm As Name

' Unprotect your workbook and sheets

    ThisWorkbook.Unprotect "onlyVbaPassWord"
    For Each Sht In ThisWorkbook.Sheets
        Sht.Unprotect "onlyVbaPassWord"
    Next Sht

' Unprotect the appropriate named ranges and protect the others

    For Each Nm In ThisWorkbook.Names

        ' The next `Select Case` statement implements your business logic
        Select Case Application.UserName
            Case "Me", "Myself", "I"
                Nm.RefersToRange.Locked = False
            Case "You", "Yourself", "Thou"
                If Nm.Name Like "*_4U" Then
                    Nm.RefersToRange.Locked = False
                Else
                    Nm.RefersToRange.Locked = True
                End If
            Case Else
                Nm.RefersToRange.Locked = True
        End Select
    Next Nm

' Protect your sheets and workbook again

    For Each Sht In ThisWorkbook.Sheets
        Sht.Protect "onlyVbaPassWord"
    Next Sht
    ThisWorkbook.Protect "onlyVbaPassWord"
End Sub

Upvotes: 1

Related Questions