Forum Discussion
Lock Cells If Its not Updated Today
You could also do this without VBA. With the help of validity rules, it is possible to lock one or more cells under certain conditions, so that no arbitrary data can be entered there.
Highlight the cell or range of cells
Ribbon click Data Validation,
Command data validation...
In the Data Validation dialog box,
Tab settings:
validity criteria
in the combo box Allow: select date,
Check the Ignore empty cells checkbox
In the Formula: field, enter the formula: =TODAY()
Tab error message:
Display error message checkbox... with tick
in the combo box type: select stop
In the Title or 'Error message:' text field, enter your text, such as "Invalid date".
OK button
And so that the whole thing works:
If the cell or range of cells is not empty, or contains anything other than the current date, typing in the range of cells will be blocked and the message you specified will be displayed.
Hope I was able to help you with this information.
I know I don't know anything (Socrates)
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 subThe 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)