Forum Discussion

m_eminmutlu's avatar
m_eminmutlu
Copper Contributor
Dec 01, 2022

Lock Cells If Its not Updated Today

Hello all   I have a scenario. Maybe you can help me.   I have empty cells that need to enter date by my colleagues. I want below scenario   If cell is empty its unlocked If a person enter a d...
excel
Macros and VBA
m_eminmutlu's avatar
m_eminmutlu
Copper Contributor
Dec 03, 2022

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 Sub

But 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.

NikolinoDE 

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Dec 03, 2022

m_eminmutlu 

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 Sub

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

  • m_eminmutlu's avatar
    m_eminmutlu
    Copper Contributor
    Dec 03, 2022

     

    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.

     

    NikolinoDE 

      • m_eminmutlu's avatar
        m_eminmutlu
        Copper Contributor
        Dec 13, 2022

        NikolinoDE 

         

        Thank you so much. 

         

        The one "lock_with_data_entry" lock the cells after first entry. Cells can't change/update in same day.

         

        The one "with_comment_stamp" working well. The only problem is, we need a backdoor or admin right/password for if someone do something wrong admin should change/update it with password. And column "K" (Duration(Planned)) is not locked. 

         

        Hovewer, You did really good job for me. I am so thankful to you.

Resources