Lock row after data input

[Bandito1]

Hi all,

I found some example but they are not exactly what i want.

I enter data in A2, B2, C2, D2.
When i enter data in E2 i would like that A2, B2, C2 and D2 will be locked.
When i remove the data from E2 the cells will unlock again.

And this for every row. So also for A3, B3, C3 and D3 and so on.


The example i found will lock the entire range.

Can someone help me with this?

 

[sanketsk]

Hi Try this its working for me. Theres short versions also but this code you will understand better.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="12345"
If ActiveSheet.Range("E2") <> "" Then
Range("A2").Cells.Locked = True
Range("B2").Cells.Locked = True
Range("C2").Cells.Locked = True
Range("D2").Cells.Locked = True
Protect Password:="12345"
Else
Range("A2").Cells.Locked = False
Range("B2").Cells.Locked = False
Range("C2").Cells.Locked = False
Range("D2").Cells.Locked = False
ActiveSheet.Protect Password:="12345"
End If
End Sub

Remember to Unlock only column E or E2 cell
How to unlock cell E: Select column E, right click then choose format cells, then goto Protection tab then untick Locked

 

[Bandito1]

Thanks for your reply!

I understand your code

Only my sheet has like 1256 rows, can't do that with your code without typing a lot right?

 

[Bandito1]

Someone else can help me changing this code to work for "x" amount of rows?

[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]ActiveSheet.Unprotect Password:="12345"
If ActiveSheet.Range("E2") <> "" Then
Range("A2").Cells.Locked = True
Range("B2").Cells.Locked = True
Range("C2").Cells.Locked = True
Range("D2").Cells.Locked = True
Protect Password:="12345"
Else
Range("A2").Cells.Locked = False
Range("B2").Cells.Locked = False
Range("C2").Cells.Locked = False
Range("D2").Cells.Locked = False
ActiveSheet.Protect Password:="12345"
End If 
[COLOR=#333333]End Sub
[/COLOR]

 

[Rick Rothstein]

Remove the SelectionChange event procedure that you now have and use this Change event procedure instead. With the method I have employed in this code, you do not need to lock cells nor protect the worksheet (unless you need to protect it for some other reason in which case you will have to add the password code lines to the following code)...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("A:D")) Is Nothing And Target.CountLarge > 0 Then
    If Len(Cells(Target.Row, "E").Value) Then
      MsgBox "You cannot change values in Columns A:D if cell in Column E has a value in it!" & _
             vbLf & vbLf & "The previous cell value will now be restored.", vbExclamation
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    End If
  End If
End Sub[/td]
[/tr]
[/table]

 

[Bandito1]

This is truly beautiful!

Thank you very much!