Results 1 to 4 of 4

how to auto lock a cells value after data entry

This is a discussion on how to auto lock a cells value after data entry within the Excel Questions forums, part of the Question Forums category; I wish to know is it possible to lock a cell automatically after data is entered and saved ..ie I ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    61

    Default

    I wish to know is it possible to lock a cell automatically after data is entered and saved ..ie I enter data in rows is certain columns so that after I save it, so that who ever opens it the next time should not be able to change that cell value but only enter new data in a subsequent row cell..and also is there a way where only I can change that cell value by using say a password..

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Maybe this can help you.

    I created a new workbook, selected all the cells, and UNCHECKED the locked option for all of them.

    Next, I protected the sheet, using as password

    Password

    then, I put this code in the Sheet's module.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect "Password"
    Target.Locked = True
    ActiveSheet.Protect "Password"
    End Sub

    Then, everytime I change a cell, it becomes locked, that means that you can't write on it again.

    You could modify them later using Tools | Protect | Unprotect sheet, and supplying the password (Password in this case)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    61

    Default

    thanks Juan...

  4. #4
    New Member
    Join Date
    Nov 2013
    Posts
    2

    Default Re: how to auto lock a cells value after data entry

    Hi, the code works well, but I need to lock the entire row or a defined range in that row, like A1:G1. Also, as a correction if the user wants to leave a cell blank, it should stay unlocked as if there was never anything entered in it before. Scenario. User enter the date in A1 and later wants to delete it. He will first unprotect the sheet and clear the contents of he cell A1. At this point, base on the condition that the cell is empty it should stay editable.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com