automatically lock a cell (excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

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

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

Reply | Quote