Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Lock cells vba

Posted on 2011-10-05
7
Medium Priority
?
249 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:kwatt562
7 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 36919423
Please try removing the "" from this:

If Target.Column = "26" Then

Like this:

If Target.Column = 26 Then
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36919452
Also, you don't need both:

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

Just:

        Target.EntireRow.Locked = True

Kevin
0
 

Author Comment

by:kwatt562
ID: 36919584
Thanks for your comments but it still fails to lock the rows and protect the sheet
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36919772
And this must be

If UCase(Target.Value) = "APPROVED"

Open in new window

0
 

Author Comment

by:kwatt562
ID: 36919847
Yeah that was it, couldnt seee the wood for the trees, thanks for pointing it out
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36919864
Thanks for the points, but I think you should acknowledge others' contributions too.
0
 

Author Comment

by:kwatt562
ID: 36919945
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question