Forum Discussion
Lock Cells If Its not Updated Today
Hello Nikolino,
I think i explain myself wrong. People can enter any other future date. The mail goal is when they enter a date today, they can update/change it only today. Tomorrow it should be locked.
The entry date in cells is irrelative. People will enter future date into the cells.
The point is;
We want to people enter a date "when they start to work on that task". But we also want that people can't change what they enter into cells tomorrow or future.
I've been looking for a quick solution and fished an example from the internet :)).
Is untested and must also be adapted to your needs.
Private SubWorksheet_Activate()
Dim AlleA As Range
Set AllA = Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 1))
ActiveSheet.Unprotect "x" ' Enter the administrator password instead of "x".
ActiveSheet.Cells.Locked = True ' lock everything first
For Each Cell In AlleA
If Cell.Value > Range("A1").Value - 2 Then ' if at least 2 is less than A1
'Cell.EntireRow.Locked = False ' unlock this entire row
Range(Cell.Offset(0, 3), Cell.Offset(0, 14)).Locked = False ' the 12 cells next to _
unlock the date
End If
Next Cell
ActiveSheet.Protect "x" ' here too
end sub
The macro only frees cells with the following properties, if all 3 properties are true at the same time: - there is an entry in column A - the entry is > A1 -2 days, i.e. yesterday, today, tomorrow, the day after tomorrow, etc. - and the columns are between C and O.
Hope I was able to help you with this information.
Thank you for your understanding and patience
I know I don't know anything (Socrates)
Hello NikolinoDE
I think i still couldn't explain myself very well. The entry in cell is not important. I don't care what people enter in cells. I only care "CELL'S UPDATE DATE" "THE TIME WHEN PEOPLE CHANGE OR ENTER ENTRY IN CELL" "THE TIMESTAMP OF CELL"
I found below code for find the date when cells updated;
Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset (0,1)
.Value = Now
.NumberFormat = "DD/MM/YYYY"
End With
End SubBut I couldn't find the code for "Lock the cell if value is older than today"
I hope i can explain myself better now.
If you have time, I am open for any short call or meeting on Teams, Zoom etc.
Here is a small example with VBA.
File is included with the example.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit Sub 'Editing multiple lines is caught If Target = "" Then Target.Offset(0, 1).ClearContents Else Target.Offset(0, 1) = CDate(Format(Now, "dd.mm.yyyy")) End If End SubHope I was able to help you with this info.
I know I don't know anything (Socrates)
Hello NikolinoDE,
Thanks for your support. You helped me a lot. I just want one more last point.
https://drive.google.com/file/d/1nP5ockJ1D1jwj3IWZBRNQfIUuPQUEnpy/view?usp=sharing
I am sharing my excel with you. I want to implement your codes to "I", "J", "K", "L" column.
People enter date into "I", "J", "L" column. And they can change cells only first entry day.
People enter number into "K" column. And it's same for this column.
People can't change cells if it's not updated today.
You can use "Update Date" sheet for everything.