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 a cell containing data validation post selection of result

Discussion in 'VBA Macros' started by Vipullade, Dec 5, 2017.

  1. Vipullade

    Vipullade New Member

    Messages:
    13
    I have a file which has data validation in a column, the values are from 1-5, post the user selecting any one number the cell should get locked, please assist with this.

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    Please upload a file which is not protected ; otherwise , post the password for the worksheet protection.

    Narayan
  3. Vipullade

    Vipullade New Member

    Messages:
    13
    Hi
    sorry, the passwrd is Big5@123
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    See the attached file. The first entry is already locked.

    Narayan

    Attached Files:

  5. Vipullade

    Vipullade New Member

    Messages:
    13
  6. Vipullade

    Vipullade New Member

    Messages:
    13
    I need to use this sheet multiple times, which means I would have to unlock the cells every time It is to be given to the user
    Am I correct on this
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    If you want , a macro can be written for this too.

    Narayan
  8. Vipullade

    Vipullade New Member

    Messages:
    13
    That would be a great help, I am myself learnihng VBA but still in a very nascent stage....Really appreciate yout help
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    See the attached file.

    You will have to run the macro named ResetLocks.

    Note that both macros make use of a named range called DataEntryCells.

    Narayan

    Attached Files:

  10. Vipullade

    Vipullade New Member

    Messages:
    13
    Thanks buddy for this
  11. Vipullade

    Vipullade New Member

    Messages:
    13
    Hey Narayan, need help in this, I modified the reset code to reset the cell values to "Please select", but due to this all the cells in the range are locked, i need them to be unlocked and lock only if the values are between 1-5 both inclusive..can you help please
  12. Vipullade

    Vipullade New Member

    Messages:
    13
    Public Sub ResetLocks()
    [dataentrycells].Locked = False
    [dataentrycells].Value = "Please Select"
    End Sub
    Code for your reference
  13. Vipullade

    Vipullade New Member

    Messages:
    13
    got it buddy, I modified the code
    NARAYANK991 likes this.
  14. Vipullade

    Vipullade New Member

    Messages:
    13
    Sorry to be a bother but Now I need to link the locking code to a command button, can you help on this as i tried doing it but dont know what i am missing, I put an active x button and tried linking the locking code but it wont work.....Please help
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    Upload your file with the button , code ,...

    Narayan
  16. Vipullade

    Vipullade New Member

    Messages:
    13
    The password is Big5@123, i have used the same code you provided narayan, in the command click sub but it is not working, i am mssing on something, but could not figure it out.. please help

    Attached Files:

  17. Vipullade

    Vipullade New Member

    Messages:
    13
    what I am looking for is now that the cells remain unlocked untill the submit button is selected, once the submit button is selected, all the cells in the range f8:f106 in the test sheet should be locked
  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

    sathishsusa likes this.
  19. Vipullade

    Vipullade New Member

    Messages:
    13
    Thanks, I should have gone to basics....really appreciate your help

Share This Page