2

I need help in locking specific range of cells based on the selection of value from list from another cell.

To be specific, I have created data validation list for columns N5 to N36, upon selection of value "Exist" from the cell N5, I want to lock that specific row O5 to U5.

i.e "Exist" in N6 would lock O6 to U6 and so on.

Similarly for the other rows till N36.

And if the user selects "Does not Exist", then I would want those cells to remain unlocked and editable similar to the above condition.

I have tried macros from various forums using my very basic knowledge of using macros, but most of those lock the entire sheet.

Code I tried:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("N5:N36")) Is Nothing Then ActiveSheet.Unprotect If Target.Value = "Exist" Then Range("O" & Target.Column & ":U" & Target.Column).Select Selection.Locked = False Else Range("O" & Target.Column & ":U" & Target.Column).Select Selection.Locked = True End If End If ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

I would really appreciate your quick help.

Thanks in Advance.

Improve this question
5
  • you should unlock all cells that are not in that range and then protect the sheet, I'll look into that code a little bit ;) Commented Oct 30, 2014 at 7:43
  • Hi @GoosvandenBekerom, Below is one of the codes which I used actually, where after my selection from one cell, entire sheet is getting locked. Commented Oct 30, 2014 at 7:50
  • Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("N5:N36")) Is Nothing Then ActiveSheet.Unprotect If Target.Value = "Exist" Then Range("O" & Target.Column & ":U" & Target.Column).Select Selection.Locked = False Else Range("O" & Target.Column & ":U" & Target.Column).Select Selection.Locked = True End If End If ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Commented Oct 30, 2014 at 7:53
  • dont add that code in the comments, add it in your question. I posted an answer below, you could check that out Commented Oct 30, 2014 at 7:54
  • i added the code to your question that you put in the comments Commented Oct 30, 2014 at 7:59

2 Answers 2

Reset to default
2

Is this what you are trying (Tried And Tested)? Also see THIS. It's worth a read.

Private Sub Worksheet_Change(ByVal Target As Range) Dim rw As Long Dim sPass As String '~~> Password sPass = "BlahBLah" On Error GoTo Whoa '~~> For excel 2003 use .Count instead of .CountLarge '~~> In case of multiple cells were changed If Target.Cells.CountLarge > 1 Then Exit Sub Application.EnableEvents = False If Not Intersect(Target, Range("N5:N36")) Is Nothing Then If UCase(Trim(Target.Value)) = "EXIST" Then rw = Target.Row With ActiveSheet .Unprotect sPass .Cells.Locked = False .Range("O" & rw & ":U" & rw).Locked = True .Protect Password:= sPass , DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End If End If Letscontinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume Letscontinue End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for helping Siddharth. But when I am the running the code, I am facing an error as "Compile error Expected Sub, Function or property". Please suggest how to proceed further. Thanks again.
Ah Ok. I got it... See the updated post. You may have to refresh the page to see it
Works like a charm. Thanks a lot Siddharth for your help. Could you please let me know the change you have made, it would help me understand. Thanks once again.
See the edit history. :) I had forgotten to put "=" in Password:= sPass
0

you could do something like this:

Sub LockCells() 'unprotect the sheet ActiveSheet.Unprotect 'unlock all cells Cells.Locked = False Cells.FormulaHidden = False Dim cell As Range 'find all cells that need to be locked For Each cell In Range("N5:N36") If cell = "Exist" Then Range("O" & cell.Row & ":U" & cell.Row).Locked = True Range("O" & cell.Row & ":U" & cell.Row).FormulaHidden = True End If Next cell 'protect the sheet ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub
Improve this answer

6 Comments

Will try this and get back to you. Thanks for your help.
I would want to modify my question a little. If I select "Exist" in N5, then I would want to lock only that row i.e. O5 to U5. Similarly for all individual rows from N5 to N36. Please suggest the appropriate answer. Apologies for the change.
make changes to your question don't put them in comments like i said before, change your question above or I can't help you
Apologies for adding it in the comment. I have edited my question accordingly. Kindly help. Thanks
What you want is possible, and with the info i gave you you could do it yourself, it would make a very slow code tho if you want to achieve that. I will change my answer a little bit and you would be able to figure out the rest
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.