CODEWITHSUNDEEP
vbaexcel
user3812753
user3812753

Reputation: 79

Excel VBA: inject code into sub to store logs (timestamp)

I need to save timestamp and ID of the person who is running the sub (the sub is being ran every hour by different (3-4) people). I will get the username with:

*.Value = Environ("Username")

The main question, is how I would be able to store the logs? Indeed saving them to different spreadsheet is not an option, because people will notice that something is opening and closing. Saving in notepad is also not an option, because the file needs to be on shared drive and everyone has different letters for the drive, so we do not want to get critical error here when finding the path.

Ideally, it could save the timestamps in locked and hidden sheet in the same *.xlsm file. In column A the username and column B the timestamp (hour ; minute ; second). My question here is, do anyone have any ideas, to make it as simple as possible and what code to use, without having the code shining per 15-20 rows so even the newbies could spot it through alt+f11 and etc?

Thank you very much guys.

Upvotes: 0

Views: 277

Answers (1)

David G
David G

Reputation: 2347

Yes. Create a sheet called 'Log' and set it to Very Hidden

Sheets("Log").Visible = xlVeryHidden

That way nobody can un-hide it. After this I suggest you lock your code so nobody can look inside the code in Tools, VBAProject Properties, Protection (From the VBE).

Of course the code would be a very simple code stored in the sub itself, something like

dim nextrow as long
nextrow = Sheets("Log").Cells(Sheets("Log").Rows.Count, 1).End(xlUp) + 1
Sheets("Log").Range("A" & nextrow) = username
Sheets("Log").Range("B" & nextrow) = timestamp

Upvotes: 1

Related Questions