+ Reply to Thread
Results 1 to 11 of 11

disable input to cells when a cell has a specified value

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    disable input to cells when a cell has a specified value

    From B2 to B11 if any one of the cell input a value of x, the other cells are not allowed to input any value.
    Reset B2 to any other value, then other cells are allowed to enter input again

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: disable input to cells when a cell has a specified value

    Is it only B2 that will have the value of X? Are there values in the other cells of B2 to B11?
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,942

    Re: disable input to cells when a cell has a specified value

    If B2 is the ONLY cell you will be testing for, highlight B3:B11, use Data Validation, custom, and enter this

    =B2<>"x"
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: disable input to cells when a cell has a specified value

    Thanks for the input.
    I think I do not post the right question.
    I have 5 cells for examples, they are A2, B2, C2, D2 and E2. Only one of the cell can be selected. When one of the cell is selected and input a value of x, then the other cells are disable and cannot enter any value. To enable the other cells again, the cell with value has to be clear.
    I know it is easier to use a dropdown but it is a scale to input.

  5. #5
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: disable input to cells when a cell has a specified value

    Hi,

    as far as I understood this should help:LockCells.xlsm

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  6. #6
    Registered User
    Join Date
    04-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: disable input to cells when a cell has a specified value

    Thank you.

    It works, great code, but it is not exactly what I want because I did not make my question clear.

    I just want to control 10 cells in the same row. If I enter a value to any one of the cell, the other cells int the same row are locked/disable to enter any value.
    When I remove the value of the cell in the row I have inputted, all the cells in this row are unlocked/enabled again.
    In this case, one can only enter a value in one of the 10 cells.

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: disable input to cells when a cell has a specified value

    Hello dxcheung,

    I am pretty sure that the attached WorkBook does what you want.

    Also see the "Please consider" note at the bottom of this post.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  8. #8
    Registered User
    Join Date
    04-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: disable input to cells when a cell has a specified value

    Thanks for you help Winon.

    The script is very useful but it is not exactly I want.
    In the file you attached, when you put a value in L2, from A2 to J2 cells are locked and you can reset L2 for all cells to work again. It is different from what I want.
    I need that when I input a value to any one from A2 to J2, except the cell I input a value, the other cells are locked. (for example if I input a value to A2, B2 to J2 are locked but after I clear A2 and I enter a value to B2, then A2, C2 to J2 are locked.

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: disable input to cells when a cell has a specified value

    Hello dxcheung,

    O.K. then.

    How about this one?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: disable input to cells when a cell has a specified value

    [SOLVED]

    Thank you very much Winon.

    It is exactly what I needed - excellent way to use data validation.

    I cannot add another star as I do not know how to spread the reputation around. I really appreciate you of the immediate help and the excellent answers.

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: disable input to cells when a cell has a specified value

    Hi dxcheung,

    You are welcome.

    Glad I could help!

    Please go to your first post of this Thread, click on Thread Tools, and mark it as solved.

    Cheers!

+ 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