tracking changes

[Neodubois]

Hi

I would like to have a xls sheet which would track changes done in another sheet with details and the user if possible


thanks

[Bob Phillips]

Multiple workbooks or just one? All worksheets?

[Neodubois]

well first for just one workbook but prob for several and for sure for all worksheets

if not possible just 1 workbook and 1 sheet would already be a good thing

[Bob Phillips]

Not a problem, just basic details.

[vba]
Option Explicit

Public WithEvents App As Application
Private NextRow As Long
Const SHEET_NAME As String = "Tracking"

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Parent Is Me Then
Application.EnableEvents = False
With Me.Worksheets(SHEET_NAME)

.Cells(NextRow, "A").Value = Target.Address(, , , True)
.Cells(NextRow, "B").Value = Target.Text
.Cells(NextRow, "C").Value = Environ("Username")
.Cells(NextRow, "D").Value = Now
.Cells(NextRow, "D").NumberFormat = "dd mmm yyyy hh:mm:ss"
.Columns("A:C").AutoFit
NextRow = NextRow + 1
End With
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_Open()
Dim Sh As Worksheet

Set App = Application
On Error Resume Next
Set Sh = Me.Worksheets(SHEET_NAME)
On Error GoTo 0
If Sh Is Nothing Then

Set Sh = Me.Worksheets.Add(after:=Me.Worksheets(Me.Worksheets.Count))
Sh.Name = SHEET_NAME
End If
With Sh

If .Range("A1").Value = "" Then

Sh.Range("A1").Value = "Changed"
Sh.Range("B1").Value = "Value"
Sh.Range("C1").Value = "User"
Sh.Range("D1").Value = "Timestamp"
Sh.Range("A11").Font.Bold = True
NextRow = 2
Else

NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End If
End With
End Sub
[/vba]

This is workbook event code, and goes in the tracker workbook.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code