Dynamic Named Range with Password Protection

Here is an example of what I think you want to do. Column C has a list of user ids. Column D is where you want to enter data.

When you change selection and select a cell in column D, an "unlock" program runs. It gets the login USER ID from the Environment and compares it to the value on the same row, one column to the left. If there is no match, the sheet stays protected. If there is a match, the sheet is unprotected, the selected cell is unlocked and the sheet is re-protected. The only active cell is the selected cell.

When the user makes an entry into this cell, the cell is then again locked.

The Password to protect the sheet is "Password" - this is set at the top of the code in the Module Modlock. You can change it and then put a password on the VB project so people won't see the password because they won't be able to see the code.

Here is the code on the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim UserName As String

If Left(Target.Address, 2) = "$D" Then
    UserName = Environ("username")
MsgBox Target.Address & " - " & UserName
    If Target.Offset(0, -1).Value = UserName Then
        UnlockCell Target
    End If
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserName As String

If Left(Target.Address, 2) = "$D" Then
    LockCell Target
End If

End Sub

Here is the code in the ModLock module

Const Password = "Password"

Sub LockCell(CellRef As Range)
Application.EnableEvents = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=Password
CellRef.Locked = True
Application.EnableEvents = True
End Sub

Sub UnlockCell(CellRef As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:=Password
CellRef.Locked = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=Password
Application.EnableEvents = True
End Sub

Here is how to "fix" your situation.

Unprotect the sheet.
Select all cells
Format all cells to be unlocked.
Format columns C & D to be locked
Reprotect the sheet