How to lock an excel row when certain cells are no longer blank?

Question

I am editing my question to make it more explicit.

I have an excel with school tuition payments in a table where each row is a monthly payment. I want to lock all the rows that have been paid (that is if there is amount in column E) so that it cannot be erased or changed without a password.

The rows that have been paid are not necessarily in order.

I have tried a ton of different VBA codes that I have found here. But none of them have worked for me (some of them work but they only lock the rows that I´m editing at that moment and not all the rows that have values in Column E)

I have never worked with VBA...But I have to fix this. I am in a Mission School in Ecuador and I don´t know anyone who can help me... I really need to fix this soon because the secretary has already accidently erased data (!!!)

Answer 1

First you protect the worksheet with UserInterfaceOnly. For this topic see Error 1004 when setting Range.Locked.
Place the following Sub in a VBA module, put the cursor somewhere into it and press F5:

Public Sub LockActiveSheet()
  ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

Then you can unlock empty cells and lock cells containing value in columns A:E with the following code in the same VBA module. Make sure that the worksheet 'PENSIONES' is open. Then put the cursor into it and press F5.

Public Sub LockNonEmptyCells()

  Dim lngLastRow As Long        'index of the last used row
  Dim rngAE As Range            'the range from cell A3 to Exxx (last used row)
  Dim i As Long                 'counter in For...Next
  
  lngLastRow = [A1].SpecialCells(xlLastCell).Row 'get index of last used row
  Set rngAE = [A3].Resize(lngLastRow - 2, 5) 'get data range
  With rngAE.Cells              'cycle through all cells
    For i = 1 To .Count
      .Item(i).Locked = Not IsEmpty(.Item(i)) 'unlock empty cells, lock cells with value
    Next i
  End With

End Sub

For future editing you can paste the following code in the code module of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)  
  If Target.Column <= 5 Then Target.Locked = True
End Sub

This will lock the cell directly after you have entered a value.

Answer 2

Lock Row If Not Blank

• Adjust the values in the constants section.

Standard Module e.g. Module1

Option Explicit

Sub lockNonBlankRows()
    
    Const wsName As String = "Sheet1"
    Const FirstRow As Long = 2
    Const Cols As String = "A:E"
    Const pw As String = "123"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' Create a reference to the non-empty range.
    Dim rg As Range
    Dim cCount As Long
    With ws.Columns(Cols).Rows(FirstRow)
        cCount = .Columns.Count
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        Set rg = .Resize(lCell.Row - .Row + 1)
    End With
    
    ws.Unprotect pw
    
    Dim rrg As Range
    ' Loop through the rows of the non-empty range.
    For Each rrg In rg.Rows
        ' Check if all cells in the current row range are blank.
        If Application.CountBlank(rrg) = cCount Then
            rrg.Locked = False
        Else
            rrg.Locked = True
        End If
    Next rrg
            
    ws.Protect pw
    
    wb.Save

End Sub

• Additionally, you could call this procedure in some of the ThisWorkbook event procedures (not all of them), e.g.:

ThisWorkbook Module

Option Explicit

Private Sub Workbook_Open()
    lockNonBlankRows
End Sub

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    lockNonBlankRows
End Sub