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: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
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,122,848
Messages
5,598,435
Members
414,238
Latest member
juxion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top