Lock row based on value in first column

[Elraw]

Hello everyone,
I'm struggling to get this code right. Hopefully someone here can steer me in the right direction and help me out.

LOCK	PERSON	DATE	CONTROL no	Value 1	Value 2
	Person 1	01-01-2018	1234	15	55
	Person 2	20-02-2018	5678	20	42
	Person 3	15-04-2018	9012	34	37

<tbody>
</tbody>

Above is a sample of what I'm trying to accomplish, possibly by VBA.
The sheet I'm working with is a password protected sheet, with only the cells unlocked that may have data entered into them.
However, I want the data in the rows to be locked after the first cell in the row (LOCK row) has value YES.
If the data in cell A2 is YES, data in cells B2:F2 cannot be edited anymore. If the data in A2 is NO, editing is possible again.
First time I tried this with Data Validation, but the problem there was the data could not be changed, however the content could be cleared.

I've found some code that worked, but this only works on 1 cell or row. I need it to work for the full sheet, each row independently.
As the sheet might content hundreds of rows, of course I cannot just copy paste the code into oblivion.
The VBA code is tried was as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect ""
    If Range("A2") = "NO" Then
        Range("B2:F2").Locked = False
    ElseIf Range("A2") = "YES" Then
        Range("B2:F2").Locked = True
    End If
    ActiveSheet.Protect ""
End Sub

Hope everything is clear, and someone can help me out here.
Thanks in advance.

Kind regards,
Elraw

 

[nemmi69]

Hello everyone,
I'm struggling to get this code right. Hopefully someone here can steer me in the right direction and help me out.

LOCK	PERSON	DATE	CONTROL no	Value 1	Value 2
	Person 1	01-01-2018	1234	15	55
	Person 2	20-02-2018	5678	20	42
	Person 3	15-04-2018	9012	34	37

<tbody>
</tbody>

Above is a sample of what I'm trying to accomplish, possibly by VBA.
The sheet I'm working with is a password protected sheet, with only the cells unlocked that may have data entered into them.
However, I want the data in the rows to be locked after the first cell in the row (LOCK row) has value YES.
If the data in cell A2 is YES, data in cells B2:F2 cannot be edited anymore. If the data in A2 is NO, editing is possible again.
First time I tried this with Data Validation, but the problem there was the data could not be changed, however the content could be cleared.

I've found some code that worked, but this only works on 1 cell or row. I need it to work for the full sheet, each row independently.
As the sheet might content hundreds of rows, of course I cannot just copy paste the code into oblivion.
The VBA code is tried was as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect ""
    If Range("A2") = "NO" Then
        Range("B2:F2").Locked = False
    ElseIf Range("A2") = "YES" Then
        Range("B2:F2").Locked = True
    End If
    ActiveSheet.Protect ""
End Sub

Hope everything is clear, and someone can help me out here.
Thanks in advance.

Kind regards,
Elraw

Try using

[COLOR=#333333] Row("2:2").Locked = True
[/COLOR]

 

[Elraw]

Thanks for responding nemmi69.
I just tried to change it, but it gives me a Compile error (Sub or Function is not defined).
Full code I tried - I am zero VBA experience so I might be doing it wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect ""
    If Range("A2") = "NO" Then
        Row("2:2").Locked = False
    ElseIf Range("A2") = "YES" Then
        Row("2:2").Locked = True
    End If
    ActiveSheet.Protect ""
End Sub

However, looking from a logical point of view this doesn't work either.
As I tried to explain, I don't need it JUST for row 2, but for the entire sheet.
I.e. if Cell A2 contains YES, block B2:F2. If Cell A3 contains YES, block B3:F3, if Cell A4 contains YES, block B4:F4, etc. etc.

Hope it's clear enough now.
Appreciate your time and help.

 

[nemmi69]

Sorry miss an 's'

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect ""
If Range("A2") = "NO" Then
Rows("2:2").Locked = False
ElseIf Range("A2") = "YES" Then
Rows("2:2").Locked = True
End If
ActiveSheet.Protect ""
End Sub

 

[Elraw]

Thanks. This works, however not the way I want it to work.
Column A should always be editable. With this code it locks the entire Row 2, so also the Cell that should be editable (A2).

Also, this code still doesn't work as I want it to work, namely to have each row individually be locked by the first cell in the column.

Anyone might have an idea to how this is possible?

 

[nemmi69]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserRow As Long
'assuming your range is rows 2 to 20
UserRow = Target.Row
If UserRow >= 2 And UserRow <= 20 Then
    ActiveSheet.Unprotect ""
    If UCase(Range("A" & UserRow).Value) = "NO" Then
        Range("B" & UserRow & ":XFD" & UserRow).Locked = False
    ElseIf UCase(Range("A" & UserRow).Value) = "YES" Then
        Range("B" & UserRow & ":XFD" & UserRow).Locked = True
    End If
    ActiveSheet.Protect ""
Else
    MsgBox "Row " & UserRow & " is outside range"
End If
End Sub

 

[Elraw]

Thanks a lot nemmi69!
This seems to work really good.

The number of rows won't be unlimited. But say I want this code to work on the first 500 rows, I simply have to change the 5th line as follows?

If UserRow >= 2 And UserRow <= 500 Then

Thanks in advance again.

 

[nemmi69]

That's it and to restrict it to column A

Dim UserRow As Long
'assuming your range is rows 2 to 500 and only for column 1 (A)
UserRow = Target.Row
If UserRow >= 2 And UserRow <= 500 And Target.Column = 1 Then

 

[Elraw]

I just tested it, and it seems to work good.
Only problem I have now is, that whatever I type in columns B and further, I get the error "Row X is outside range" (where X is the row number I'm currently on).

Value in Column A = YES => I cannot change anything, as supposed to so this is working perfect
Value in Column A = NO => I can edit and type just fine, only thing is I keep getting the error message. It's just a message by the way, it doesn't restrict me from actual editing. This is working fine.

What should I change to get rid of this message? Or just remove that complete line

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserRow As Long
'assuming your range is rows 2 to 500 and only for column 1 (A)
UserRow = Target.Row
If UserRow >= 2 And UserRow <= 500 And Target.Column = 1 Then

    ActiveSheet.Unprotect ""
    If UCase(Range("A" & UserRow).Value) = "NO" Then
        Range("B" & UserRow & ":XFD" & UserRow).Locked = False
    ElseIf UCase(Range("A" & UserRow).Value) = "YES" Then
        Range("B" & UserRow & ":XFD" & UserRow).Locked = True
    End If
    ActiveSheet.Protect ""
End If
End Sub

 

[nemmi69]

Remove

[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]    MsgBox "Row " & UserRow & " is outside range"[/COLOR]

This was just used to show me anything outside the desired range.