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

Osama_bb11 22 Posts Sunday May 25, 2014Registration date September 25, 2014 Last seen - Jul 25, 2014 at 07:03 AM - Latest reply: Osama_bb11 22 Posts Sunday May 25, 2014Registration date September 25, 2014 Last seen
- 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

See more 

4 replies

TrowaD 2387 Posts Sunday September 12, 2010Registration date July 9, 2018 Last seen - Jul 28, 2014 at 11:45 AM
0
Thank you
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
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
TrowaD 2387 Posts Sunday September 12, 2010Registration date July 9, 2018 Last seen - 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
Osama_bb11 22 Posts Sunday May 25, 2014Registration date September 25, 2014 Last seen - Aug 4, 2014 at 02:50 PM
0
Thank you
Thank you very much , it works fine