Lock Cells At Midnight

**gandolff** · 01-27-2009, 08:37 PM

Hi,

Anyone know of a way to "lock" a cell so that it can't be changed (edited/deleted) on any date after that which the original data was entered on. That is, if I enter "10" into cell A10 on Jan. 30th 2009, that cell can't be altered on the 31st, etc. etc.?

Thanx,
Jeff

**Richard Buttrey** · 01-27-2009, 08:45 PM

Hi,

You could make A10 the subject of a Worksheet Selection_Change event, and build into the event a line of code to record the current date in another cell every time A10 changes, and a line of code to check the date cell against the current time, and disallow an entry if the date is different.

HTH

**gandolff** · 01-27-2009, 09:00 PM

Hi HTH,

Yeah, I just don't know exactly how to (actually, it'll be cells A6:A36). I do have a date-stamp (WS change macro) that records the current date in column D every time a change is made to column A (i.e., changes to A8 will record the date the change was made into D8). I just want A6:A36 to act such that once data is entered therein, it can't be changed/deleted on any date thereafter.

Thanx,
Jeff

**Richard Buttrey** · 01-27-2009, 09:16 PM

Hi,

The following procedure should accomplish what you want.

Please Login or Register  to view this content.

HTH

**gandolff** · 01-27-2009, 09:40 PM

Hi again & thanx,

I tried that code but, even after advancing the PC date, I am still able to edit/delete data in cells A6:A36 (which, per an existing macro, screws up the date-stamp I have in column D). I get the message box, but not the cell locking (of A6:A36 when future PC dates are set when testing)... Perhaps there's a way to set a condition off D6:D36 (the date-stamp column) such that when TODAY()< any D6:D36 cells' dates, then lock the corresponding cell in A6:A36...?

Jeff

**gandolff** · 01-27-2009, 11:00 PM

OK, let me put it this way:

WHEN ATTEMPTING TO EDIT A CELL IN COL. A (say, A30), "LOCK THIS CELL" IF THE DATE IN THE CORRESPONDING CELL OF COL. D (D30) IS <TODAY(). BUT "ALLOW" IF THIS CELL (OF COL. D) IS >TODAY().

That's really all I'm looking for here...

Thanx,
Jeff

**Richard Buttrey** · 01-28-2009, 06:13 AM

Hi Jeff,

I don't experience the problem you outline. I have both advanced my PC date to check this, and also, but independently advanced the date in column D to > today. In both cases the macro works as you require.

Are you sure you have created this in the Worksheet_Change event?
If so, can you upload your version of the workbook so we can take a look?

Rgds

**gandolff** · 01-28-2009, 11:24 AM

OK, I'll get back to you tonight, after work...

Tanx again,
Jeff

**gandolff** · 01-29-2009, 11:51 PM

Hi,

I scrapped that WS I was working on. But the bottom line is that, when I pasted that above code into any 2003/XP Wks, all I get is a pop up message, but no locking of cells. I can still edit/delete at will.