+ Reply to Thread
Results 1 to 7 of 7

Lock cell if value in another cell equals "Complete"

  1. #1
    Registered User
    Join Date
    07-17-2014
    Location
    Sacramento, CA
    MS-Off Ver
    2007
    Posts
    6

    Lock cell if value in another cell equals "Complete"

    How do I lock a cell if the value of a cell if the value of another cell has the value "Agency" and unlock it if it doesn't?

    Thus if B1 = Agency, C1 is unlocked and D1 is locked, and if other is selected C1 is locked and D1 is unlocked, and if B1 is null, so are C1 and D1? (and so on for B2/C2/D2?) The number of records entered (thus rows) can vary from 0 to several hundred.

    I'd read that the code below would work if I put it in the Data Validation formula box for cell D1, but it did not. The cell was always unlocked and the the value in B1 displayed False when the condition was met. I couldn't put it in C1 because the Data Validation function is already in use for its pull down menu.

    Please Login or Register  to view this content.
    Here is what I am trying to do:

    The excel spreadsheet resembles a hard copy of a data entry form that the user is entering the data into. One column in the form asks if the data recorder was employed by a government agency or other. If "government agency" is selected then C1 is unlocked and the user selects the agency from a pull down menu created using the Data Validation function. It "other" is selected, then C1 is locked and D1 is unlocked and the user enters the data recorders name. You cannot have a value in both C1 and D1, one must be null if the other one has a value. The only time they can both be null is if cells A1 and B1 are both not null (which indicates that there is actually a valid record being entered.)

    Does anyone have an idea of how I might approach this issue? I have searched the internet for a solution for two days now and still cannot find an answer that works well for me. Please keep in mind that I am a beginner and to provide comments so I understand the whys.

    A million thanks to all assistants in advance.

    BTW: Please note Title is supposed to say "Agency" not "Complete". Tried to reedit post to change "Complete" in title to "Agency" but couldn't.
    Last edited by NewbieX; 05-29-2015 at 02:23 PM.

  2. #2
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Re: Lock cell if value in another cell equals "Complete"

    I think you may need to upload a sample excel file
    Regards
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,902

    Re: Lock cell if value in another cell equals "Complete"

    Quote Originally Posted by NewbieX View Post
    I'd read that the code below would work if I put it in the Data Validation formula box for cell D1, but it did not. The cell was always unlocked and the the value in B1 displayed False when the condition was met. I couldn't put it in C1 because the Data Validation function is already in use for its pull down menu.

    Please Login or Register  to view this content.
    To start with, this works fine. See attached file. Perhaps you made an error in the Data Validation settings.

    Managing C1, though, is more difficult. One option you have is to use VBA to control this. I have a VBA solution that I will attach--but right now the forum is not rresponding.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,902

    Re: Lock cell if value in another cell equals "Complete"

    Here you go
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-17-2014
    Location
    Sacramento, CA
    MS-Off Ver
    2007
    Posts
    6

    Re: Lock cell if value in another cell equals "Complete"

    You are correct. It does work as I wrote it. The problem I had was that the formula in cell H2 was returning FALSE. I have to take a closer look at the VBA file you posted and try to decipher what it is doing exactly but I trust it works great. Thanks for the help.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,902

    Re: Lock cell if value in another cell equals "Complete"

    You don't mention anything at all about H2 in your description. How does that affect B, C, and D?

  7. #7
    Registered User
    Join Date
    07-17-2014
    Location
    Sacramento, CA
    MS-Off Ver
    2007
    Posts
    6

    Re: Lock cell if value in another cell equals "Complete"

    It doesn't. I meant B1/B2, etc....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete Rows if a cell does not equal "Finished" or "Complete"
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-27-2013, 12:40 PM
  2. [SOLVED] Countif a cell equals "x" and cell above is "y"
    By Bishonen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2013, 07:35 AM
  3. User to input character "A", but cell will show time stamp, and lock the cell.
    By matrix1108 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2012, 01:02 PM
  4. [SOLVED] Delete row if cell in Column N equal "Different" or Column P equals "Blank"
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2012, 08:25 AM
  5. Replies: 11
    Last Post: 02-08-2012, 12:36 PM

Tags for this Thread

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