Lock range of cells until a single cell is edited

Hi All,

Not sure if something similar is already posted, however my VBA experience is non existent.

Please see attached file.

What I am trying to achieve is that when the spread sheet is opened cells C8 to F17 are completely locked so that no data can be entered. When cell C2 is edited and a value entered, then cells C8 to F17 are unlocked and editable.

This is where I am struggling, cell C2 is a predefined list using data validation. Cells C8 to F17, will either be a predefined list, date input only, number input only or free text input only all cells having data validation for these already.

To simplify this example more, in cells C8 to C17 is a predefined list using data validation. Then when I open the spread sheet and Cell C2 is blank, I cant adjust any cells in C8 to C17. When I pick an item from cell C2, then cells C8 to C17 become active and able to select from the list for that cell.

I believe there is no way to do this using just data validation and protection of the sheet only and requires some sort of VBA code.

Thanks.

Hi,

Cells are only locked when Sheet Protection is switched on.
So first change the C2 protection setting to 'Unlocked'
Then Protect the sheet.
Now add the following Sheet change macro

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("C2") Then
        ActiveSheet.Unprotect
        Range("C8:F17").Locked = False
        ActiveSheet.Protect
    End If
End Sub

Hi,

Perhaps you'd better upload the workbook that contains actual values in the drop down cells so that we have a complete picture.

You should not have needed to lock any cells since they are locked by default when you protect the sheet. You only need to unlock C2.

See attached.

I'm not sure whether there is any implication in the statement "then the corresponding cells to become active, without needing to enter a password."

I don't know what you mean by 'corresponding cells' since your original specifically mentioned making all the range C8:F17 (now c6:f15) unlocked and editable.

I've also added code to the workbook open event that will lock C6:F15.