Locking an empty cell after data entry

jt1234

New Member
Joined
Jul 30, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?).

VBA Code:
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,141
Messages
5,570,409
Members
412,323
Latest member
DemonX
Top