Lock cell in column based on value of cell in previous column

[dna247]

Hi there,

I can see that this question has appeared many times before in slightly different forms, so apologies if I'm repeating a question from before.

My issue is that I would like to be able to lock the cell in column L, based on a text input in column E. Column E inputs are based on a Data Validated List ("In Development", "Approved", "Complete", "Not Progressing"), so won't vary at all, and column L will only be unlocked if "Not Progressing" is selected (in order to give a reason).

This should then fill down into every row of the worksheet.

As a VBA novice, I'm struggling to amend previous script to fit my requirements.

Greatful for any help provided.

[Mumps1]

Start by unlocking all the cells in your worksheet, protect the worksheet and then try this macro:

 Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Dim bottomE As Long
    bottomE = Range("E" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("E2:E" & bottomE)
        If rng = "Not Progressing" Then
            Cells(rng.Row, "L").Locked = True
        Else
            Cells(rng.Row, "L").Locked = False
        End If
    Next rng
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

[dna247]

Thanks for the help Mumps1, but this still seems to allow me to edit all of the cells in column L.

All cells are unlocked, and I have protected the sheet just not with a password.

[Mumps1]

When you run the macro, all cells in column L should be locked where the corresponding cell in column E is "Not Progressing". When I tried it on a dummy file, it worked properly. Can you post a copy of your file to make it easier to find the problem?

[dna247]

Thanks - file attached

[Mumps1]

You can achieve what you want in two ways. 1. If you want to lock column L automatically when you choose "Not Progressing" in the drop down list in column E, you have to copy and paste the following macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in column E and the macro will run automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    If Target = "Not Progressing" Then
        Cells(Target.Row, "L").Locked = True
    Else
        Cells(Target.Row, "L").Locked = False
        End If
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

2. If you want to run the macro manually, use the following macro in a regular module:

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Dim bottomE As Long
    bottomE = Range("E" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("E5:E" & bottomE)
        If rng = "Not Progressing" Then
            Cells(rng.Row, "L").Locked = True
        Else
            Cells(rng.Row, "L").Locked = False
        End If
    Next rng
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

Use one or the other, not both.

[dna247]

This is great - and works perfectly. Thank you so much!

[Mumps1]

My pleasure.