VBA: locking rows in Table based on another column's data.

[d5678]

Having trouble finding VBA code that addresses a table instead of just a pre-selected set of cells.

I have a large table that will continue to have rows added to it as users add new entries - so I need this to be dynamic rather than selecting a range of cells (ie, I don't want this to be A7:Q47, as the table will keep getting longer. I need the code to address A:Q).

In Column R of the table (column is named 'Placement Created'), I want to be able to have users enter the word 'Done', and have that cause the entire rest of the row (A:Q) to become protected so that other users cannot change previously entered data.

Thanks!

 

[mumps]

Start by unlocking all the cells in all the columns where you will have data including column R. Then protect the sheet with a password. Next copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Replace "mypassword" with your actual password (two occurrences). Close the code window to return to your sheet. Enter "Done" in any row in column R and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="mypassword"
    If Target = "Done" Then
        Range("A" & Target.Row & ":Q" & Target.Row).Locked = True
    End If
    ActiveSheet.Protect Password:="mypassword"
End Sub