Locking a range based on a value of a cell

Solved/Closed
Osama_bb11
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014
- Jul 25, 2014 at 07:03 AM
Osama_bb11
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014
- Aug 4, 2014 at 02:50 PM
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

3 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
Jul 28, 2014 at 11:45 AM
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.
0
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
Aug 4, 2014 at 11:59 AM
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
0
Osama_bb11
Posts
22
Registration date
Sunday May 25, 2014
Status
Member
Last seen
September 25, 2014

Aug 4, 2014 at 02:50 PM
Thank you very much , it works fine
0