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

Please Login or Register  to view this content.

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.

Hi,

In that case you must be doing something different. When I open the workbook I can't click in any cell other that C2. When I select in C2 then the table of cells becomes available.

Do you not see this behaviour?

Are you perhaps going on from that and deleting any C2 value and expecting the table of cells to be locked? If so that is an additional requirement that wasn't mentioned in your original and hence I haven't coded.

Hi,

In that case try the attached.

A word of caution. One of the reasons my amended code didn't originally work was because you've merged cells C2:G2. In the attached I've coded around that so that they are still merged. However be aware that most of us avoid merged cells like the plague. they just cause too many problems and they will undoubtedly trip you up at some stage.