Lock row based on value in first column

Elraw

New Member
Joined
Aug 31, 2018
Messages
5
Hello everyone,
I'm struggling to get this code right. Hopefully someone here can steer me in the right direction and help me out.

LOCKPERSONDATECONTROL noValue 1Value 2
Person 101-01-201812341555
Person 220-02-201856782042
Person 315-04-201890123437

<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:
Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hello everyone,
I'm struggling to get this code right. Hopefully someone here can steer me in the right direction and help me out.

LOCKPERSONDATECONTROL noValue 1Value 2
Person 101-01-201812341555
Person 220-02-201856782042
Person 315-04-201890123437

<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:
Rich (BB code):
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
Code:
[COLOR=#333333] Row("2:2").Locked = True
[/COLOR]
 

Elraw

New Member
Joined
Aug 31, 2018
Messages
5
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.

Code:
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

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 31, 2018
Messages
5

ADVERTISEMENT

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

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
How about

Code:
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

New Member
Joined
Aug 31, 2018
Messages
5

ADVERTISEMENT

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?
Code:
If UserRow >= 2 And UserRow <= 500 Then

Thanks in advance again.
 

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
That's it and to restrict it to column A

Code:
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

New Member
Joined
Aug 31, 2018
Messages
5
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
Code:
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

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Remove
Code:
[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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,247
Messages
5,527,625
Members
409,777
Latest member
jamilowella

This Week's Hot Topics

Top