Locking an empty cell after data entry

[jt1234]

Hi,

Is there a way to lock a cell after data entry, with specific named users having access to write in any empty cell, but not able to overwrite data that has already been entered? I'll explain in more detail below..

I found a method of locking a cell with a password after data entry (i.e. if a cell is empty, anyone is able to enter data into that cell, then it becomes locked after) using VBA coding (found on How to lock or protect cells after data entry or input in Excel?).

Dim mRg As Range
Dim mStr As String
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
    mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A1:F8"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
     mStr = mRg.Value
End If
End Sub

However, I need some additional functionality; I have a worksheet on a shared network drive and anyone is able to view the worksheet, but I have 3 users that enter data into the spreadsheet A,B and C - if A writes in an empty cell, users B and C should not be able to overwrite the cell contents, but A can, similarly if user B writes in an empty cell, A and C should not be able overwrite the cell contents but B can. I believe each user would need a separate password, but it would be ideal if they were able to do this seamlessly without needing to enter a password every time. I understand that it is possible to assign certain ranges to certain users, but the cells that they need to edit vary and often overlap.

It is important that these three users can't overwrite the other user's data (so a shared password isn't an option) as when this happens, the other user doesn't know about it.

Hope this makes sense! Is this possible to do in excel?

Kind regards,
Jack

 

[Yongle]

How about using a hidden sheet named Editor
- write user's name to same cell in Editor
- use Editor as lookup to determine if cell can be unlocked by current user

If you want help let me know