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]

are we talking about the average user here or some kind of specialist?

Anyone who can search the internet. No specialist knowledge needed.

 

[Joe4]

Probably the best you can do is something like this:
1. Lock the timestamp cells down.
2. Have your VBA code unlock the cells, make the timestamp update, and then re-lock the cells.

Further, to make sure people enable VBA/Macros, I have seen people put code in the Save Event of the Excel file to "hide" and lock the sheet (so it is not visible). And then have VBA code in the Open Event of the worksheet, that unlocks and unhides the sheet. So, if they don't Enable Macros/VBA, the sheet will not be visible for them.

That would be enough to keep most users from messing with the timestamps, but as Fluff pointed out, anyone who was really intent on messing it could easily defeat the security with the help of the internet. As such, Excel is not the most secure program. Whether any of the suggestions mentioned here would be enough to satisfy the auditors requirements is a question for them to answer.