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

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
My pleasure & thanks for the feedback.
 

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Unfortunately security in Excel is non-existent & therefore easily bypassed. So there is no way to guarantee the accuracy of the stamp.
 

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
That does not account for the fact that anyone can bypass the security.
 

paigemaddi

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

Board Regular
Joined
Apr 28, 2016
Messages
134
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,283
Members
412,375
Latest member
BRJoeyMelo
Top