Protect cells with data upon saving for multiple tables in multiple sheets, remaining cells should be editable when opened

Question

I am working on an excel sheet to track down the issuance of devices. I want all the members of my team to be able to edit the sheet, but once they fill the fields and save, those fields where data is entered should be locked. Code for multiple tables in multiple sheets. later on no one should be able to edit the rows with data in it, but should be able to edit the empty rows and save. is it possible for someone to Guide me on this?

Answer 1

You can achieve this with VBA. To guide you a little bit:

Select range you want to enter data. Lock sheet (with or without password).

In VBAProject add this code to ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ActiveSheet.Unprotect Password:=""
    Target.Locked = True
    ActiveSheet.Protect Password:=""

End Sub

Replace Password:="" if password was used to lock sheet with Password:="your password"

On entering data it will unlock sheet, set cell protection status to locked and then lock sheet.

Result:

Drawback is that it will set any cell to Locked upon entering data. If you want to leave some cells editable multiple times you will have to set ranges to be locked and check if Target is in any of those ranges.