1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lock and unlock cells based on condition

Discussion in 'Ask an Excel Question' started by rupal1234, Oct 22, 2018.

  1. rupal1234

    rupal1234 New Member

    Messages:
    22
    I want to give the user options which are conditional on another cell. For example, in cell B4 is a dropdown of numbers:If number is less than 12 than cell I4 will be grayed out, & be locked. And if number is greater than 12 than cell I4 will be unlocked




    I guess I have to do something in visual basic but I never used it before, so I would appreciate it if you could explain every single step [​IMG]


    I appreciate your help. Thanks!

    Attached Files:

  2. Belleke

    Belleke Active Member

    Messages:
    472
    Your file is missing.
  3. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    hmm ... Cell i4 has a formula ...
    What would be Your idea of locking?
  4. rupal1234

    rupal1234 New Member

    Messages:
    22
    i want to hide cell if i select number less than 12 based on dropdown than I4 should be locked and grayed out
  5. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    'hide', 'locked and grayed out'
    ... would mean something like this ...

    Attached Files:

    Belleke likes this.
  6. rupal1234

    rupal1234 New Member

    Messages:
    22
    unable to open up your attachment
  7. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    Saved same file with other format...
    Can You open files which has marcos?

    Attached Files:

    rupal1234 likes this.
  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    449
    I do not think locking is relevant to the problem you have described. That simply prevents the user from changing or overwriting your formula.

    In the attached I have conditionally formatted the cell so that zeros are greyed out and the number format either renders 0 as a blank or "N/A" meaning 'not available'.

    Sorry about changing your formulas to structured references; I have a blind spot when it comes to reading direct references - it is all a jumble of meaningless characters to me ;).

    Attached Files:

  9. rupal1234

    rupal1234 New Member

    Messages:
    22
    Thankyou so much :)
  10. rupal1234

    rupal1234 New Member

    Messages:
    22
    Thankyou so much :)
  11. rupal1234

    rupal1234 New Member

    Messages:
    22
    Now If i select GI Round wire from dropdown G4 than it should locked and grayed out I4 and if i select GI Flat strip from dropdown G4 than it should locked and Grayed out H4
  12. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    449
    @vletm
    You have done what the OP requested but I still struggle to see the point (by the way, I liked the ASE routine). Unlocking a formula cell achieves nothing of value whilst the formatting could equally be achieved using conditional formatting.
  13. rupal1234

    rupal1234 New Member

    Messages:
    22
    Your code is working but now i want this functionality also but i dont know how to achieve this both
  14. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    It would be ... to get needed parameters in one time.
    Even 'minor' change would mean a lot of changes...
    ... as You would notice.

    Peter Bartholomew
    True, sometimes 'the point' would be challenge to find out ...
    In this time, OP uses terms 'locked'
    ... so OP got 'locked' and that can use this way too.

    Attached Files:

  15. rupal1234

    rupal1234 New Member

    Messages:
    22

    In this when i click on g4 and select ground wire than it should lock cell I4 and if i click on flat strip then it should hide H4
  16. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    ... and if You click B4 any time and
    do or try to do selection
    ... what would happen or not?

    Attached Files:

  17. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    This would have more clear rules ...

    Attached Files:

  18. rupal1234

    rupal1234 New Member

    Messages:
    22
    This file is not opening
  19. vletm

    vletm Excel Ninja

    Messages:
    4,299
    rupal1234
    I tried to ask with Reply #7 something
    ... still waiting answer.

    Attached Files:

Share This Page