Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    automatically lock a cell (excel 97)

    Is it possible to have a cell automatically lock after a user has input data?

    eg. a time sheet, the person enters their hours for today, and the admin would like to prevent them from going back in later to change the hours.

    Thanks for any ideas on this.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatically lock a cell (excel 97)

    Yes, there are several ways that this can be done. One would be to protect the cell after something has been entered. What do you want to trigger the protection? The user entering data (which means the user can't go back and correct an error)? The administrator doing something? Saving or opening the workbook after something was entered? Something else?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: automatically lock a cell (excel 97)

    What I am looking for is some way to automatically lock the cell after the user has entered data.
    I know how to lock cells and then protect a worksheet. In this situation, I would not know when the user entered the data. I would like to have it so that once they entered data, and moved to another cell, the cell containing the entered data would automatically lock, preventing them from changing the contents. If there was an error they would have to contact the admin to change it.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatically lock a cell (excel 97)

    Ok, here is some code that should get you started. First, select all of the cells on the worksheet that you want the user to be able to modify. Then select Cells from the Format menu, and remove the check mark from next to "Locked" on the protection tab. Then insert the code below into the Worksheet Change event routine for the worksheet.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:B50")) Is Nothing Then
    ActiveSheet.Unprotect ("MyPassword")
    For Each oCell In Intersect(Target, Range("A1:B50"))
    If oCell.Value <> "" Then
    oCell.Locked = True
    End If
    Next oCell
    ActiveSheet.Protect ("MyPassword")
    End If
    End Sub
    </pre>


    This code assumes that the cells where you want this to happen are in the range A1:B50 on that Sheet1
    Legare Coleman

Posting Permissions

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