Is there a way to auto-lock a cell after a date is entered?


What I'm trying to do is have a date entered in B2 and then have the cell auto-lock so the date can't be changed by another user. I know how to lock select cells, protect sheet and all that jazz but I just don't know if this is possible. Let me quickly explain how the excel sheet will be used. I have dispatch (for a trucking company) fill out a truck repair order for our maintenance and then we are setting up another computer down at the shop that mirrors what the dispatch sees. I just don't want an employee to be able to change a date after it is entered and I don't want dispatch to have to go through the process of locking and unlocking cells. Any help would be great. Thanks!




 You need this macro:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value <> "" Then

    ActiveSheet.Protect Contents:=False
    Range(Target.Address).Locked = True
    ActiveSheet.Protect Contents:=True

End If

End Sub

Insert it into the sheet where the dates go. To do that, right-click the desired sheet tab and click View Code and input the code into the window that opens.

This assumes that the dates will be in column A. Change the 1 to 2 for column B and 3 for C etc.

Before you run the macro though, make sure to "unlock" all of the cells in the worksheet.


Answer the Question

You must create an account to use the forum. Create an Account or Login