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 date into cell today, the cell can only change today. After midnight the cell should locked.

If a cell updated yesterday or past, the cell locked.

 

Important thing is not the date entered in to the cell, important thing is the date that when cell updated.

 

Could you please help me to create VBA code of this?

excel
Macros and VBA

11 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 02, 2022

    m_eminmutlu 

    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.

     

    NikolinoDE

    I know I don't know anything (Socrates)

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

      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. 

       

      NikolinoDE 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Dec 02, 2022

        m_eminmutlu 

        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

         

        NikolinoDE

        I know I don't know anything (Socrates)

         

Resources