Mr. Rothstein posted an answer to this question
What if I need to lock a range of cells after input in the same range. To be clear, For the range C5:H5, I want to lock the remaining cells in that range
if there is any data input. I already have data validation going on in these cells because they contain drop-down menues. If I delete that data the cells in that range will unlock. I would like to do this for about 15-20 different ranges of cells. I hope it
was alright to mention your username Mr. Rothstein since it was such a great response to the initial question. Hope you can help.
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?
Your response WAS
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?
Your response WAS
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
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
WORKED GREAT BUT......
What if I need to lock a range of cells after input in the same range. To be clear, For the range C5:H5, I want to lock the remaining cells in that range
if there is any data input. I already have data validation going on in these cells because they contain drop-down menues. If I delete that data the cells in that range will unlock. I would like to do this for about 15-20 different ranges of cells. I hope it
was alright to mention your username Mr. Rothstein since it was such a great response to the initial question. Hope you can help.