Results 1 to 27 of 27

Lock cell based on value in another cell

Threaded View

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    Sweden
    MS-Off Ver
    O365
    Posts
    33

    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.
    Attached Files Attached Files
    Last edited by Arphaxad; 06-29-2020 at 04:25 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code to lock cell based on anotehr cell value not working
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2016, 12:09 PM
  2. Replies: 1
    Last Post: 05-11-2015, 06:02 PM
  3. lock and insert formula in a cell based on selection of text in another cell
    By alirazafazal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2014, 06:08 AM
  4. Lock/Unlock a cell based on the value selected from drop down in adjacent cell
    By mahesnrm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2013, 08:28 AM
  5. Replies: 1
    Last Post: 09-12-2013, 10:38 PM
  6. Replies: 1
    Last Post: 03-11-2013, 03:10 PM
  7. Replies: 1
    Last Post: 03-10-2013, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1