Locking a range based on a value of a cell [Solved/Closed]

Report
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014
-
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014
-
Hello,

first I would like to thank every one working at kioskea

my issue is , I want to lock a set of cells ( A2:M2) if the cell M2="app" and password should be needed to open it again

your help is highly appreciated

thanks in advance

Related:

3 replies

Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
Hi Osama,

By default all cells are blocked. So first unblock them by selecting all cells (Ctrl+a or click on the spot above the 1 of row 1) and going to cell properties (Ctrl+1 or right-click > cell properties).
Go to the final tab called protection and uncheck the blocked option.

Now select A2:M2 and check the blocked option again.

Once that is done, implement the following code (right-click sheets tab > view code > paste code) and try it out:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M2")) Is Nothing Then Exit Sub

If Target = "app" Then
    ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

End Sub


Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Thank you very much this is actually good , but Maybe i should be more clear

I have a data sheet with many rows at column M it might be "app" in this case only i want the related row (A..L) to be locked with a password , I hope this more clear

please help
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
Hi Osama,

For that a minor change in the code is needed:
Change (from the second code line):
Range("M2")
into:
Columns("M:M")

Also make sure you have the right cells blocked.

Best regards,
Trowa
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014

Thank you very much , it works fine