0

I need to protect all the cells in a particular row if my user enters Y (yes) into a column of that particular row which indicates that the user has reviewed the data and that it is correct. I have not been able to figure out how to make this happen. Does anyone know how to do this? Thanks so much, Elias

Elias A. Stern-Rodriguez
  • 19
  • 3
  • 1
    You can use the `Change` event to trigger your macro when a "Y" is entered in the specific column. You'll need to unprotect the sheet, lock the cells on that row, then re-protect the sheet. – Tim Williams Jul 13 '15 at 20:46

1 Answers1

1

As per your request and Byron's comments, I edited the code. The code should be pasted into the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Exiter
Set Sh = Target.Parent
        If Target.Value = "Y" And Target.Column = 1 Then
            Unprotect Password:="WHATEVER"
            For Each curRow In Sh.UsedRange.Rows
                If Sh.Cells(curRow.Row, 1) = "Y" Then
                    Sh.Cells(curRow.Row, 1).EntireRow.Locked = True
                Else
                    Sh.Cells(curRow.Row, 1).EntireRow.Locked = False
            End If
            Next
            Sh.Protect Password:="WHATEVER"
        End If
Exiter:
Application.EnableEvents = True
End Sub
Abe Gold
  • 2,307
  • 17
  • 29
  • The standard way to check if a cell is in a `Range` for change events is by using `Intersect`. Also, most people use the `Worksheet_Change` event which then removes the need to check if the sheet is correct. Finally, this code should probably disable events during its execution since it is making further changes to the `Worksheet`. Not sure if it will cause an infinite loop, but it's good practice. All of those topics are covered here: http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640. – Byron Wall Jul 13 '15 at 22:29
  • thank you. is there any way to make it so that the cells in a row could be editable until the user inputs Y in the checked column for that row? – Elias A. Stern-Rodriguez Jul 14 '15 at 14:52
  • I just added two lines to the code to prevent that from happening. See above. – Abe Gold Jul 14 '15 at 19:24