How to stop manual edits after macro is input

[paigemaddi]

Hi everyone,

I am new here and no expert at excel, but I am working on a solution for a report at work.

I found a macro to use that automatically puts the "input time" and the "last updated time" in the workbook. So whenever I enter any data, it will update those two cells automatically. However, at my work we have regular audits and oversight testing and they will check if the timestamp is accurate and trustworthy. Here's my problem:
After I input the macro and the timestamps are automatically generated in the cells, I can still go in and manually edit the cells in which the macro is running.

My question is, is there a way I can lock in the macro so that I am unable to manually edit any of those cells. In other words, can the macro override any manual edits I do?
I know that I can lock or protect the entire worksheet, but they will say that I could have unlocked it at any time and manually changed the timestamp if I wanted to.

Although the macro is running, if I can change that cells anyways, the auditors will not trust that the timestamp is true.

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
There is no way of guaranteeing the accuracy of a time stamp, anyone could simply prevent macros from running and then change the stamp.

 

[paigemaddi]

Hi & welcome to MrExcel.
There is no way of guaranteeing the accuracy of a time stamp, anyone could simply prevent macros from running and then change the stamp.

That is unfortunate. Thank you for your quick response though! And thanks for the welcome.

 

[Fluff]

My pleasure & thanks for the feedback.

 

[Nothnless]

Sounds like the timestamp just needs to be on another sheet and password protect that sheet. This way they cant say you just unlocked it if you don't have the password for that sheet. You'll also need to password protect the Visual Basic editor as well, so that no one can go there and just look at the password.

 

[Fluff]

Unfortunately security in Excel is non-existent & therefore easily bypassed. So there is no way to guarantee the accuracy of the stamp.

 

[Nothnless]

Hi & welcome to MrExcel.
There is no way of guaranteeing the accuracy of a time stamp, anyone could simply prevent macros from running and then change the stamp.

In the on change event of the spread sheet you can say if this cell equals True then unprotect the sheet that users can use.
An on open event needs the change the cell to True
a before close event needs to change the cell back to False.

This way if macros are disabled then the cell never changes to True so the sheet remains protected.

 

[Fluff]

That does not account for the fact that anyone can bypass the security.

 

[paigemaddi]

I appreciate both of your responses and understand both. The auditors may have to settle for a compromise of sorts and accept the fact that we did our best to update it with timestamps for them, but unless our tech team constructs a whole new program for this report that would set solid timestamps that cannot be changed, this is the best we've got.

 

[Nothnless]

That does not account for the fact that anyone can bypass the security.

I think you could say the same thing about the lock on a safe but are we talking about the average user here or some kind of specialist?

Anyway, Fluff is a MVP and Moderator so for anyone reading this I would definitely stick with his advise here.