macro for recording changings

[maksinx]

hello,

i was just wonderin if it is possible to record every changings on once excel sheet,
what happens is i have an excel file which is located in a server at work and 3-4 people enters data in it end of the day if someone makes any mistakes i want to find out which data changed to what..

i know excel has got track changes function but this file is not a share file.

thanks in advance

[mdmackillop]

How about
[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "C:\AAA\Test-" & Application.UserName & ".xls"
End Sub
[/VBA]

[Bob Phillips]

Why not just log the changes, before and after details with user id.

[maksinx]

dear xld,
how can i do that can you be more specific.

lets say file called tracker and in "O" share drive and user id's as follows
k.mark & b.dennis & m.iron

thanks

[mdmackillop]

Add a sheet called "Log"; place this code in ThisWorkbook module

[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Application.UserName
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = Target.Formula
End With
Application.EnableEvents = True
End Sub
[/vba]

[johnske]

Use On Error Resume Next after Application.EnableEvents = False in Malcolms code to cater for Event errors generated by clearing a selection of multiple cells, deleting rows etc.

[maksinx]

dear all,
can i also see changing colors deleting information or rows etc,

pls advice

[mdmackillop]

Where?
Are you going to have any form of protection on your workbooks? I'm not sure about recording all events that can occur.

[maksinx]

yes the actual file is password protected,

[Bob Phillips]

Add a sheet called "Log"; place this code in ThisWorkbook module

[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Application.UserName
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = Target.Formula
End With
Application.EnableEvents = True
End Sub
[/vba]

I would use

Environ("UserName")

rather than

Application.UserName

as malcolm did, it is more robust.

[Bob Phillips]

Add a sheet called "Log"; place this code in ThisWorkbook module

[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Application.UserName
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = Target.Formula
End With
Application.EnableEvents = True
End Sub
[/vba]

I would also capture the previous value with the SelectionChange event and record that.

[mdmackillop]

Incorporating suggestions
[VBA]Option Explicit

Dim Previous As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Previous = Target.Formula
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Environ("UserName")
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = "'" & Target.Formula
.Offset(1, 4) = Previous
Previous = ""
End With
Application.EnableEvents = True
End Sub
[/VBA]

[Bob Phillips]

Look at that, before the thought is out of my head, Malcolm has coded it

[lucas]

That's pretty slick. I like the log sheet as record.

[lucas]

for anyone who might be interested....this adds the date column which is handy too:
[VBA].Offset(1, 5) = Now[/VBA]