Limit updating capabilities for select people

[Mich6661]

I have a file that is updated by 2 groups of people.
For some of the cells, I don't want group 1 to be able to update but they need to be able to update other cells.
It would be the same for group 2.
therefore.... Column A,B,C & D can only be updated by members within Group 1 & Columns E,F,G&H can only be updated by members within Group 2.

I thought using the Protect Sheet icon would do this but I can't get it to work

Any suggestions?

 

[mikerickson]

Would Group 1 even have the data needed to update a forbidden cell?

I would suggest telling members of Group 1 to not change those particular cells. Perhaps coloring the forbidden cells to remind them.
Similarly for Group 2.

 

[Mich6661]

Would Group 1 even have the data needed to update a forbidden cell?

I would suggest telling members of Group 1 to not change those particular cells. Perhaps coloring the forbidden cells to remind them.
Similarly for Group 2.

Unfortunately simply having coloured cells wouldn't be an effective solution.
I have "caught" each group changing data or dates to suit their group KPI's. Even in the "forbidden cells

Thank you anyway ?

 

[mikerickson]

I don't know what a KPI is.
But employees deliberately entering false data in order to get some personal benefit sounds like this is an HR issue rather than an Excel issue.

One quick method would be to have a user enter one of two passwords. If they enter the group1 pw, then A:D are unlocked. If the group2 password, E:H are unlocked, if the user submits neither password, all of A:H are locked. (The locking of the other cells on the sheet is at your discression.)

This routine (in the ThisWorkbook codemodule) involves three passwords
strGroup1, strGroup2 are the passwords for the two groups. adminPW is the password used to Protect the sheet after the group passwords have determined which ranges should be locked.

' in ThisWorkbook code module

Private Sub Workbook_Open()
    Call GroupAccess
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    GroupAccess (True)
End Sub

Public Sub GroupAccess(Optional LockOut As Boolean)
    Dim uiVal As String
    Dim strPrompt As String
    Dim strGroup1 As String, strGroup2 As String
    Const adminPW As String = "admin"
    strGroup1 = "group1": strGroup2 = "group2"
  
    If LockOut Then
        uiVal = vbNullString
    Else
        uiVal = Application.InputBox("What is your group password", _
            Default:="I'm not in a group", Type:=2)
    End If
  
    With Sheet1
        .Unprotect Password:=adminPW
        .Range("A:H").Locked = True
        If uiVal = strGroup1 Then
            Range("A:D").Locked = False
        ElseIf uiVal = strGroup2 Then
            .Range("E:H").Locked = False
        End If
        .Protect Password:=adminPW
    End With
End Sub

Like any security routine for Excel, this isn't very sturdy.

As is, this works on Open and Close of the workbook, but other macros could be written to call ThisWorkbook.GroupAccess when needed to switch between groups (or those who are in neither group)

 

[Mich6661]

Appreciate you taking time to respond. I will trial what you have suggested.
Thank you very much