Audit Trail

[Rich]

I would like to build an audit trail into a spreadsheet so that anyone who
opens the file is is logged in a worksheet with user name and time stamp.
What's the easiest / best way to achieve this?

 

[ozgrid.com]

Look into Track Changes in the Excel Help.

Or, for a VBA method;
http://www.ozgrid.com/VBA/track-changes.htm

 

[Mike H]

Rich,

Alt+F11 to open VB editor. Double click 'ThisWorkbook' and paste the code
below in on the right. It uses a worksheet called "Audit"

Private Sub Workbook_Open()
Dim LastRow As Long
Set sht = Sheets("Audit")
LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
sht.Cells(LastRow, 1) = Environ("Username")
sht.Cells(LastRow, 2) = Now
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

In case the user doesn't save it may be a good idea to save it with the
openers username by making this the last line in the code

ActiveWorkbook.Save
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Chip Pearson]

See http://www.cpearson.com/Excel/TrackingWorkbookOpens.aspx .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com