+ Reply to Thread
Results 1 to 3 of 3

"Locking" Cell OR Accepting User Input based on the value of another cell's dropdown menu.

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    Here
    MS-Off Ver
    Excel 2013
    Posts
    16

    "Locking" Cell OR Accepting User Input based on the value of another cell's dropdown menu.

    Hello everyone, very new to VBA (new= just started learning this week) and have an immediate need for a solution, so apologies if I am asking something obvious before scouring the forum/internet for a specific answer. This should be simple enough

    My scenario:

    I have a dropdown menu/data validation on one cell (we'll call "Items") and another cell called "Enter Item Manually." One of the options in the "Items" dropdown is "Blank." When "Blank" is selected, I want the "Enter Item Manually" cell to behave like an empty cell, one where a user could enter whatever value they would like to. When anything else besides "Blank" is selected from the dropdown, I want the "Enter Item Manually" cell to say "Item Selected Above" and be non-editable, or "locked" as I called it in the title of this thread.

    Currently, I'm using this simple VBA (written on the sheet object) to keep the "Enter Item Manually" cell clear when there is a Worksheet Change event:

    Please Login or Register  to view this content.
    Though this more or less does what I need, I want what I've described above to happen. Plus, worksheet change events are new to me, and I've caused problems/infinite loops when tweaking this little bit of code. I assume there is a better alternative. Any thoughts?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: "Locking" Cell OR Accepting User Input based on the value of another cell's dropdown m

    Hello HughManatee,

    Welcome to the Forum!

    Locking a cell requires the worksheet be password protected. Every cell on the worksheet is "Locked" by default. Once the password protection is set, only cells that are unlocked can be edited.

    When writing code for either a SelectionChange or Change event, you should make a habit of disabling events while the macro is running and re-enable events when the macro finishes. This prevents the looping you are experiencing.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    12-18-2012
    Location
    Here
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: "Locking" Cell OR Accepting User Input based on the value of another cell's dropdown m

    Hey Leith,

    Thank you for your response. However, I want to do things a bit simpler than what you've described. I do not really want to lock the entire workbook, or any cells for that matter, I was just using the word "lock" to describe the idea. I really just want the second cell to display "Item Selected Above" when any thing else besides "Blank" is selected.

    Also, I tried using your code (minus the ActiveSheet.Protect lines) and now my sheet wont update the second cell by clearing it like it used to. I even went back to my old code (in my first comment) but it no longer performs as it did before. Did I enable something that negates worksheet change events when I used your code? How would I find this out.

    For reference, here is the code I switched back to:

    Please Login or Register  to view this content.
    Last edited by HughManatee; 12-19-2012 at 06:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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