Protect Sheet with Different User Permissions

[FloatedWheat]

Hello,

I am working on a workbook with a lot of information and a lot of users. I have a primary worksheet that I want to lock down, say that all the data is in the range of A1:G100 of Sheet1. I want to allow only a small portion of the users, call them Group A, to edit the contents of the primary worksheet. Adding rows, deleting rows, selecting cells and changing their values, working in the worksheet like there are no protections on it. Then I want the rest of the users, call them Group B, to be able to view the sheet and sort the data. Just sorting, not editing, adding, deleting, formatting, or anything other than sorting using filters. I've been struggling to come up with the right combination of Protect Sheet settings and Allow Edit Ranges and Permissions settings. If anyone could help with this, that would be great!

Summary
Group A: Edit cells, add/delete rows, do whatever they want with the worksheet
Group B: Only sort/use filters, nothing else

 

[iggydarsa]

Not sure if you are familiar with VBA

While your excel file is open click on Alt+F11, a new window will open.
From the left pane double click on the sheet name where you want this permissions to take affect.
On the main pane paste this code:

Private Sub Worksheet_Activate()
    Select Case Environ("username")
    'Group A
    Case "myName1", "myName2"
        ActiveSheet.Unprotect
    'Group B - aka. Anybody who is not in Group A
    Case Else
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    End Select
End Sub

Change "myName1", "myName2" with the proper usernames

To know what your system recognized username is you can run this code:

Sub EnvironUserName()
    MsgBox Environ("username")
End Sub

 

[FloatedWheat]

Not sure if you are familiar with VBA

While your excel file is open click on Alt+F11, a new window will open.
From the left pane double click on the sheet name where you want this permissions to take affect.
On the main pane paste this code:

Private Sub Worksheet_Activate()
    Select Case Environ("username")
    'Group A
    Case "myName1", "myName2"
        ActiveSheet.Unprotect
    'Group B - aka. Anybody who is not in Group A
    Case Else
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    End Select
End Sub

Change "myName1", "myName2" with the proper usernames

To know what your system recognized username is you can run this code:

Sub EnvironUserName()
    MsgBox Environ("username")
End Sub

Hello, I appreciate the reply! One problem that may be true with this solution would be that the file lives in MS Teams, and multiple people are always working in this workbook. Do you know if the solution you provided would still work?

 

[iggydarsa]

Hello, I appreciate the reply! One problem that may be true with this solution would be that the file lives in MS Teams, and multiple people are always working in this workbook. Do you know if the solution you provided would still work?

That would work if you upload the latest version and they download it from there.
It would not work if they work on previously locally saved files and
not work if the excel opens on Team's (web based) UI.

Also keep in mind you might want to save your file as xlsm or xlsb instead of xlsx.