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!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Is there a way to auto-lock a cell after a date is entered?
0
Answers
0
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 SubInsert 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.