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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Sashank Vemuri
  • 23
  • 7
  • 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 ;) – Goos van den Bekerom Oct 30 '14 at 07: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. – Sashank Vemuri Oct 30 '14 at 07: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 – Sashank Vemuri Oct 30 '14 at 07:53
  • dont add that code in the comments, add it in your question. I posted an answer below, you could check that out – Goos van den Bekerom Oct 30 '14 at 07:54
  • i added the code to your question that you put in the comments – Goos van den Bekerom Oct 30 '14 at 07:59

2 Answers2

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
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 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. – Sashank Vemuri Oct 30 '14 at 10:46
  • Ah Ok. I got it... See the updated post. You may have to refresh the page to see it – Siddharth Rout Oct 30 '14 at 11:45
  • 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. – Sashank Vemuri Oct 30 '14 at 12:39
  • See the edit history. :) I had forgotten to put "=" in `Password:= sPass` – Siddharth Rout Oct 30 '14 at 12:40
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
Goos van den Bekerom
  • 1,475
  • 3
  • 20
  • 32
  • Will try this and get back to you. Thanks for your help. – Sashank Vemuri Oct 30 '14 at 07:55
  • 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. – Sashank Vemuri Oct 30 '14 at 08:04
  • 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 – Goos van den Bekerom Oct 30 '14 at 08:06
  • Apologies for adding it in the comment. I have edited my question accordingly. Kindly help. Thanks – Sashank Vemuri Oct 30 '14 at 08:11
  • 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 – Goos van den Bekerom Oct 30 '14 at 08:14
  • I changed the answer, actually this should do what you want ;) it will lock al the rows that have "Exist" in column n and the other rows will stay editable – Goos van den Bekerom Oct 30 '14 at 08:21