2016 Excel VBA Code for Locking a Cell after Data Entry

[Paul_Hossler]

try this

Also did a little formatting on the MsgBox

Option Explicit


'I assume that the entire WS is Locked = False


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cl As Range
    Dim check As VbMsgBoxResult


    For Each cl In Target
        With cl
            If Len(.Value) > 0 Then
            
                check = MsgBox("Is this entry correct?" & vbCrLf & vbCrLf & _
                    vbTab & .Value & "  (" & .Address(False, False) & ")" & vbCrLf & vbCrLf & _
                    "This cell cannot be edited after entering a value.", vbYesNo, "Cell Lock Notification")
                
                If check = vbYes Then
                    If Me.ProtectContents Then Me.Unprotect Password:="123" '   unprotect first
                    .Locked = True
                    Me.Protect Password:="123" '   not Activeshet.
                
                Else
                    .ClearContents
                    ActiveCell.Offset(-1, 0).Select '   reselect data entry cell
                End If
            End If
        End With
    Next cl


    
End Sub