How to stop manual edits after macro is input

paigemaddi

New Member
Joined
Jul 30, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Attachments

  • Timestamp macro.PNG
    Timestamp macro.PNG
    122.3 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
are we talking about the average user here or some kind of specialist?
Anyone who can search the internet. No specialist knowledge needed.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,381
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,138
Messages
5,570,378
Members
412,321
Latest member
Yusuf_A
Top