Populate cell with current date and time, then lock cell

**panny** · 12-17-2012, 12:52 PM

Hello,

I'm fairly novice to excel. I've been looking for a solution to my problem and haven't been able to find anything specific enough (that I can recognize).

I'm trying to have column A populate with the current date and time (i.e. NOW) when data is entered in column B, then have that cell lock so the date and time cannot be manually changed and it will not update if the data in column B is changed.

Thank you in advance...

**JBeaucaire** · 12-17-2012, 04:22 PM

You cannot do that with a NOW() formula, obviously, I assume that's why you're posting in the VBA forum.

So you want a macro to insert a date into column A anytime you start to fill out a data row, and once the date is entered, it cannot be changed.

1) Obviously you lock column A and protect the sheet. The keeps the users out of column A.
2) This event macro in the sheet module will enter a date in column A:

Please Login or Register  to view this content.

**panny** · 12-17-2012, 04:52 PM

Thank you for the information. It is very useful.

My apologies... I forgot to mention that I also want to share the workbook so more than one user can work in it at the same time. Is it possible to run a macro in a shared workbook? If not, is there another way this can be accomplished?

**JBeaucaire** · 12-17-2012, 06:04 PM

I don't do any of that, chaotic work environment so we don't employ anything like that in our offices. Sorry. That's a separate question for a separate thread.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**Jakobshavn** · 12-17-2012, 06:13 PM

You can do this if you allow iterative calculations:

Excel Options > Formulas > Enable Iterative Calculation and pick at least once.

Then In A1 enter:

=IF(B1="","",IF(A1="",NOW(),A1)) and format A1 as mm/dd/yyyy hh:mm:ss