How to lock cells which are automated filled

Question

I want to lock the cell after entering a value in it. When I change the value on sheet2 A1, the value should still be locked in B2.

When I enter "3" in Sheet2 A1 the number 2 should till be there.

Here the code I already have:

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.Unprotect "1234"

If VBA.IsEmpty(Target.Value) Then

    Target.Locked = False

Else
     
    Target.Locked = True

End If
Sheet1.Protect "1234"

End Sub

Answer 1

My first answer was assuming that cell Locking referred to the Range.Locked property but now I understand that was intended to refer to the cell values and preventing them from recalculating.

There are a few techniques that can be used to prevent cells from recalculating. The easiest would be to just change any formulas into a static value like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target.Value = Target.Value
    Application.EnableEvents = True
End Sub

This event will get rid of every formula after it calculates its value the first time. Every cell will just be whatever value the user enters or whatever value the formula calculates the first time.

You can limit the range that the event will operate within by using Intersect like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OperationalArea As Range, AffectedArea As Range
    Set OperationalArea = Me.Range("B2:F10")
    Set AffectedArea = Intersect(Target, OperationalArea)
    
    If Not AffectedArea Is Nothing Then
        Application.EnableEvents = False
        AffectedArea.Value = AffectedArea.Value
        Application.EnableEvents = True
    End If
End Sub

This would limit the event to only change cells within the area "B2:F10" as defined in OperationalArea.

If you don't like this idea you can try messing about with Application.Calculation but it gets very messy when you have multiple sheets or workbooks open.