VBA-Protect Row

[Jeff]

Hi All,
I want to protect a row when a specific value is entered into a cell. For
example, when column Final is changed from (blank or No) to Yes, that row is
protected (in addition to the a function already programmed).
{code for function already programmed
''''When Study Final is changed to Yes
If Target.Column = 29 Then
If Cells(Target.Row, 29).Value = "Yes" Then
''''Backlog set to 0
Cells(Target.Row, 54).FormulaR1C1 = 0
''''Protect Row
'?????
End If
End If
}

I've read enough posts stating that it's a waste of time and the row is not
well protected. I know this. The protection will prevent users from
erroneously using the row; it is not intended to prevent malicious tampering.
Thanks for any help.

 

[Mike H]

Geoff,

For this to work the sheet must be protected. So select all cells and clear
the 'Locked' status.

right click the sheet tab, view code and paste this code in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 29 Then Exit Sub 'Col AC
ActiveSheet.Unprotect Password:="mypass"
If UCase(Target.Value) = "YES" Then
Cells(Target.Row, 54).Value = 0
Rows(Target.Row).Locked = True
End If
ActiveSheet.Protect Password:="mypass"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jeff]

Mike,
Thank you. The solution works very well for our limited scope.
Jeff