asked on

Lock cells vba

Got a sheet where order entry is entered in each row, column Z of the row, "approved" is entered. When approved is entered against the order I want the worksheet to lock that row and then protect the sheet.The next orders can then be entered and each time "approved is entered in that rows column z, the row should lock.

My code is
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = "26" Then
If UCase(Target.Value) = "Approved" Then
ActiveSheet.Unprotect
Target.EntireRow.Locked = True
Target.Locked = True
ActiveSheet.Protect
End If
End If

End Sub

Bur I cant quite get it too work correctly

jppinto

Please try removing the "" from this:

If Target.Column = "26" Then

Like this:

If Target.Column = 26 Then

zorvek (Kevin Jones)

Also, you don't need both:

Target.EntireRow.Locked = True
Target.Locked = True

Just:

Target.EntireRow.Locked = True

Kevin

kwatt562

ASKER

Thanks for your comments but it still fails to lock the rows and protect the sheet

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwatt562

ASKER

Yeah that was it, couldnt seee the wood for the trees, thanks for pointing it out

StephenJR

Thanks for the points, but I think you should acknowledge others' contributions too.

kwatt562

ASKER

Appreciate everyones comments, but the "" resolution is not needed as it works with quotes on, likewise the removal Target.Locked = True doesnt effect anything it just cleans it up. Thats why I gave you all the points as you pointed out my error which was preventing the code from running.

Regards