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

 

[JB]

Rich,

Check out XLAuditTrail. That might do the trick for you. It gets embedded into your spreadsheet and tracks all activity into an audit trail. www.xlaudittrail.com

Best of luck with it.

JB

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?

--
Rich
http://www.rhodes-pefkos.co.uk
http://www.rhodes-lindos.co.uk

On Saturday, April 10, 2010 5:17 AM ozgrid.com wrote:
Look into Track Changes in the Excel Help.

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

//www.cpearson.com/Excel/TrackingWorkbookOpens.aspx[/URL] .

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