How to lock an excel sheet upon save/closing

[porfor_Las]

I use Excel spreadsheets in my workplace and we need a way to be able to stop anyone changing data once it has been entered. I would like for us to be able to edit what we are writing before saving or closing and then once closed no one can edit the cells that has data in them without a password. The sheets are currently password protected and some cells are locked like headers or cells with formulas in them, but i want to be able to write data in and know all the data is not changed without a password.

• Users can only input data at empty cells.
• Users cannot edit old data.
• Users should be able to edit their new data, until they save the file.

 

[MikeVol]

Hello @porfor_Las. Insert in the ThisWorkbook Module next code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call ProtectFilledCells
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ProtectFilledCells
End Sub

Private Sub ProtectFilledCells()
    Dim ws          As Worksheet
    Dim cell        As Range
    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
       
        With ws
            .Unprotect Password:="1234"   ' Enter your password
            .Cells.Locked = False

            For Each cell In .UsedRange

                If Not IsEmpty(cell.Value) Then
                    cell.Locked = True
                End If

            Next cell

            .Protect Password:="1234", UserInterfaceOnly:=True
        End With

    Next ws

    Application.ScreenUpdating = True
End Sub

The password in this case will be 1234, you can change/set it in the code. Enter some data on any sheet, save and close the workbook. Open the workbook again and try to change the existing data, yes, it is no longer possible to change anything on the sheets. Be careful, the code works on all sheets of the workbook. God luck.