Lock value in cells on/after a date in another cell

[madmoo84]

Hello all!

Not sure if this is even possible!

I have a tracker for orders that gets updated daily with the number of orders requested and orders delivered/fulfilled. On a second tab which is linked by a =sheetnameCell formula, is where I want to stop the data changing after a certain date so I can see at what percentage was achieved 12 weeks before the orders were due to be 100% complete.
In the attached example, the Tracker Tab is the one I want to be able to enter data in daily, the second tab SLA is the one where I need data in columns F, G and H to lock on (and after) the date in column E (just the row). As of today (20/10) the data in yellow should not change even if the data in the tracker tab changes but the orange should change. Then on the 22/10 row 21 should then lock. I hope that make sense!
I don't mind if the SLA tab has to be changed/revamped etc!

Many thanks in advance,

[Kaper]

code to be inserted in Thisworkbook module

Please Login or Register  to view this content.

open the attachment but not allow macros - you will see the unchanged sheet.
close, open again allowing macros - the formulas will be replaced with current values for dates in column D earlier than 12 weeks from today. Such check and possible changing into values will be conducted every time the file is open, so when you open it in a week next row will become blocked. By the way, I think that 2023 Jan 13 shall be blocked tomorrow.

You can also add such code before End Sub to make sure last changes are immediately saved:

Please Login or Register  to view this content.

[Marc L]

Hi Kaper !

As the code is located within the ThisWorkbook class module so you can remove ThisWorkbook. before Save statement …

[Kaper]

Indeed
But on the other hand, it makes no harm, and if for some reason the code is copied to other (standard) module will also work.

[Marc L]

Yes no harm and yes again obviously in case of a copy to another module type ...