Solved: Lock a row after a value is chosen from a drop down menu

[cduckett]

I'm very new to VBA and was wondering if someone could help me write a code to lock a row after a certain value is chosen from a drop down list in the last column (beginning with Q9) and then the file is saved.

For example, the rest of the row is filled out normally with text values, integers, and also a few choices from drop-down lists. The last column, when the answer "Yes" is chosen from a drop-down list, I have conditional formatting that causes the entire row to be highlighted.

After, the "yes" value is selected, and the user saves the spreadsheet, I would like if the entire row became locked to prevent any tampering/errors.

Right now, I have the sheets protected with a password that prevents selecting of "locked cells."

Thanks in advance!

[Bob Phillips]

What do you mean by locked? Is the sheet protected?

[BrianMH]

Add this to your worksheet code.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 And Target.Row > 8 Then
Target.Worksheet.Unprotect ("insert password here")
Target.EntireRow.Locked = True
Target.Worksheet.Protect ("insert password here")
End If[/VBA]

[cduckett]

Add this to your worksheet code.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 And Target.Row > 8 Then
Target.Worksheet.Unprotect ("insert password here")
Target.EntireRow.Locked = True
Target.Worksheet.Protect ("insert password here")
End If[/VBA]

I tried adding this to the worksheet, but anytime I enter a value into any of the cells, I get an error (Compile error: unexpected end sub)

What do you mean by locked? Is the sheet protected?

Yes the sheet is protected, I've locked cells at the top of each worksheet in the workbook so as to prevent tampering with column headers, etc.

I would like for the cells in the row to all become locked as well, and for the sheet to stay protected, so as to prevent anyone from accidentally deleting data from the previous rows

[Bob Phillips]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H2:H5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect 'Password:=password if applicable

Target.EntireRow.Locked = .Value = "Yes"
End If

ws_exit:
Me.Protect 'Password:=password if applicable
Application.EnableEvents = True
End Sub[/VBA]

[BrianMH]

my code was missing the end sub and I forgot to verify the Yes. Silly me. Just use xld's code. He is the Distinguished Lord of VBAX after all

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 And Target.Row > 8 Then and target.value = "Yes"
Target.Worksheet.Unprotect ("insert password here")
Target.EntireRow.Locked = True
Target.Worksheet.Protect ("insert password here")
End If

end sub[/vba]

[cduckett]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H2:H5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect 'Password:=password if applicable

With Target

Target.EntireRow.Locked = .Value = "Yes"
End With
End If

ws_exit:
Me.Protect 'Password:=password if applicable
Application.EnableEvents = True
End Sub[/VBA]

This worked perfectly! Thanks so much for the help!