LockCells with Workbook_BeforeSave event - MS Excel

Protecting cell contents to prevent loss of data/alteration of cell contents. This code will  lock cells after saving the excel file. This is a worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.

Before this you need to set up cell protection "Locked" to "Unlock" for all the cells you want to protect. To do this select the range of cells you want to protect and press Ctrl+1 for Format Cells, in the Protection tab un-tick the "Locked" check box then press OK.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 ActiveSheet.Protect Contents:=False
  For Each cell In Range("A1:M1000")
   If cell <> "" Then cell.Locked = True
   If cell = "" Then cell.Locked = False
  Next
 ActiveSheet.Protect Contents:=True
 Sheets("Sheet1").Protect Password:="yourpassword"
 Sheets("Sheet1").Protect UserInterfaceOnly:=True
 Columns("A:J").AutoFit
End Sub