How to make audit trail to log which persons ever edited an excel file?

**ohlalayeah** · 07-29-2012, 11:22 PM

How to make audit trail to log which persons ever edited an excel file? e.g. to record down the Author name and Company (refer to Excel menu -> File -> Properties" and date/time stamp on a worksheet. Thanks

**JBeaucaire** · 07-30-2012, 12:18 AM

This should get you started...

1) Create a sheet called AuditLog in your workbook and hide it, if you wish.
2) Open the VBEDditor (Ctrl-F11) and add this macro to the ThisWorkbook module:

Please Login or Register  to view this content.

It will put the computer username and timestamp onto the AuditLog sheet as soon as anyone opens the sheet, so you'll know everyone who opens the workbook, whether they change anything or not.

**ohlalayeah** · 07-30-2012, 12:44 AM

Thanks for your reply! I modified a little bit to capture excel username instead of windows login name. But I have 3 more requests:

1. also capture Company name in excel or full computer name stored in windows
2. capture what have been changed in cells.
3. keep up to 999 records.

Please Login or Register  to view this content.

**JBeaucaire** · 07-31-2012, 03:14 AM

Setup the AuditLog with the following Column Headers

http://screencast.com/t/tsLKQZtSCZgE

Please Login or Register  to view this content.

Then put all these macros into your ThisWorkbook module:

Please Login or Register  to view this content.

When the workbook is opened, it will record the user name and computer name. Then every edit they make will be recorded, including the prior value.

The possible values you can use for the Environs("somevaluehere") are:

ALLUSERSPROFILE
APPDATA
CLASSPATH
CommonProgramFiles
CommonProgramFiles(x86)
CommonProgramW6432
COMPUTERNAME
ComSpec
FP_NO_HOST_CHECK
HOMEDRIVE
HOMEPATH
LOCALAPPDATA
LOGONSERVER
NUMBER_OF_PROCESSORS
OnlineServices
OS
Path
PATHEXT
PCBRAND
Platform
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramData
ProgramFiles
ProgramFiles(x86)
ProgramW6432
PSModulePath
PUBLIC
QTJAVA
SESSIONNAME
SystemDrive
SystemRoot
TEMP
TMP
USERDOMAIN
USERNAME
USERPROFILE
WecVersionForRosebud.13E8
windir
windows_tracing_flags
windows_tracing_logfile

**kychang** · 02-21-2014, 04:15 AM

This script works perfectly on changes made manually onto the respective cells. However, it is not able to log in the changes to cells that changes accordingly to the IF condition input. Anyway to resolve this issue?

**JBeaucaire** · 02-23-2014, 05:24 PM

Sure, as per forum rules, open a thread of your own and ask that question specifically. You can link to and refer to this thread and this code if you wish.

**lovetofly32** · 05-22-2014, 07:17 PM

This works great when i use it for files stored on my desktop, but testing it for files on the corporate network do not yield any results. Could this be related to permissions set on the network?

**JBeaucaire** · 05-22-2014, 07:46 PM

That would be a question for another thread or forum section. As per forum rules, remember to start your own threads for your own inquiries. You can provide links to other threads you want considered in your new question. Thanks.