Lock cell based on value in another cell

So, I've watched several videos on this matter but I still can't seem to make it work. I'm not sure if it's the preconditions that are causing the confusion or something else. I'll try to be as specific as possible with what I'm trying to achieve. The target is the PLANNER sheet.

If there's text in Y6, then unlock Y7, else lock Y7. Y7 is a drop down list with pre-set text choices. If user is trying to access Y7 return message box saying "Please make a choice in the cell above first."
If there's text in Y7, then unlock Y8, else lock Y8. Y8 is a drop down list with pre-set text choices. If user is trying to access Y8 return message box saying "Please make a choice in the cell above first."
If there's text in Y8, then unlock Y9, else lock Y9. Y9 is a drop down list with pre-set text choices. If user is trying to access Y9 return message box saying "Please make a choice in the cell above first."
If there's text in Y9, then unlock Y10, else lock Y10. Y10 is a drop down list with pre-set text choices. If user is trying to access Y10 return message box saying "Please make a choice in the cell above first."

If the user makes a choice in Y7 which unlocks Y8 and later decides to delete the choice from Y7 which emptys the cell, I wan't Y8 to change from unlocked to locked automatically.
If the user makes a choice in Y8 which unlocks Y9 and later decides to delete the choice from Y8 which emptys the cell, I wan't Y9 to change from unlocked to locked automatically.
If the user makes a choice in Y9 which unlocks Y10 and later decides to delete the choice from Y9 which emptys the cell, I wan't Y10 to change from unlocked to locked automatically.

If the user makes choices for cells Y7:Y10 and decides to delete the information only in Y7, I want Y8:Y10 automatically to become empty as well and locked in addition.
If choices were made in Y7:Y10 and the user decides to delete the information only in Y8, I want Y9:Y10 automatically to become empty as well and locked in addition.
If choices were made for Y7:Y10 and the user decides to delete the information only in Y9, I want Y10 to automatically become empty as well and locked in adition.

I want this to apply only to the certain sheet I have in mind and not the others in the workbook. In addition, the user has the choice to copy this whole sheet into another sheet if he/she wishes. So I want this code to apply to the newly copied sheet as well.

Preconditions:
Upon opening the file Y6 is unlocked and Y7:Y10 are locked with a password (the whole sheet is password protected).
Cells Y7:Y10 are drop down lists with pre-set texts. There are 15 choices to make.

It sounded easy at first, but upon trying several different set ups, I can't seem to make it work. I've tried several ByVal Target As Range codes (Change/SelectionChange) but I can't seem to make it work and now I'm really confused.

I believe I just wrote the easiest and most amateur-ish piece of code, but it seems to work. What do the experts say?

Please Login or Register  to view this content.

If that is OK, is there any possibility to deny users access to the developer/macro part of the file as the passwords will be visible there (both in the module and worksheet tabs)?

EDIT: This code seems to have slowed down the file considerably. I suspect it has to do with the combination of SelectionChange and the fact that it's constantly implementing the password, but I might be wrong. Suggestions?

Below code is not exactly what you want,
but maybe you can have a look on how it work then modify it according to your need.

Pre-condition :
Cell Y6:Y10 is not locked / not password protected.

Please Login or Register  to view this content.

From the code above, there is no lock/unlock process, but something like "disabling" :
The user won't be able to fill whatever cell (between Y7:Y10) if the cell above it is blank.

Please Login or Register  to view this content.

When the user clear any cell within cell Y7:Y10, the code will clear the cell below it until Y10.

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the file, close it and then re-open it.

Please Login or Register  to view this content.

Originally Posted by karmapala

Below code is not exactly what you want,

Thank you, mate. Interesting solution. It worked when I tried it yesterday. Added a column to the sheet today, and it seems to have messed with some functions/macros as it's not working properly anymore eventhough I revised the code to accommodate for the added column. Can't remember the specific error. I'll have to check more into detail tomorrow and get back. Until then, thanks again.

Originally Posted by Mumps1

Place this macro in the code module for ThisWorkbook.

Thanks as always, mate. But this solution seems to come into conflict with another problem you solved for me the other day, namely the CLEAR button as I'm not able to use it now because it says that the data it's trying to clear (Y6:Y10) is in a password protected range.

Also, for some reason, having added a column messed up the CLEAR button as well eventhough I revised the code. I added a column before column Y, so I revised all macros for columns after column Y. But apparently thst didn't help.

Try the attached version.

Thanks, mate. But upon clicking on the CLEAR button, it seems to mess with the format in Z6:Z10. It works perfect until I use the CLEAR button. After that, the cell Z6 locks and I can't make any choice at all in Z6:Z10. Maybe always leaving Z6 unlocked since the first choice will always go in there and thus it needs to be open all the time?

Replace the current "Clear" macro with this one:

Please Login or Register  to view this content.

Try these revised macros:

Please Login or Register  to view this content.

You are very welcome.

Try the attached file.

I made some changes to both the Save and Copy buttons. If you have changed the file, depending on what those changes were, the macros may not work properly now. You would have to attach a copy of your revised file for me to use as a test.

I tested the macros I used in the file in Post #15 and they worked properly in your revised file.

Try the attached file.

Originally Posted by Mumps1

Try the attached file.

Thanks. Works like a charm. Just realised I attached an outdated version of the file. I've tried to implement your changes to my most recent file, but it seems as if the chart pastes to the wrong area. I've tried to change it to AC3:AF21, but it still seems to misplace when I SAVE the sheet. It copies the chart to AB2:AF18 instead of AC3:AF20.

Replace this line of code in both the "Save" and "Copy" macros (two occurrences in each macro)

Please Login or Register  to view this content.

with this line:

Please Login or Register  to view this content.

Originally Posted by Mumps1

Replace this line of code in both the "Save" and "Copy" macros (two occurrences in each macro)

Please Login or Register  to view this content.

with this line:

Please Login or Register  to view this content.

Thanks, but it doesn't work. The SAVE button copies the chart to column AB and not AC. And the COPY button creates two charts now, one which is wrongly placed above one that seems correctly placed.

Try the attached file.

Try:

Please Login or Register  to view this content.