PDA

View Full Version : macro for recording changings



maksinx
03-15-2007, 01:42 PM
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
03-15-2007, 05:38 PM
How about
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "C:\AAA\Test-" & Application.UserName & ".xls"
End Sub

Bob Phillips
03-16-2007, 02:07 PM
Why not just log the changes, before and after details with user id.

maksinx
03-17-2007, 02:27 AM
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
03-17-2007, 03:27 AM
Add a sheet called "Log"; place this code in ThisWorkbook module

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

johnske
03-17-2007, 05:58 AM
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
03-17-2007, 01:19 PM
dear all,
can i also see changing colors deleting information or rows etc,

pls advice

mdmackillop
03-17-2007, 01:25 PM
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
03-17-2007, 01:55 PM
yes the actual file is password protected,

Bob Phillips
03-17-2007, 02:01 PM
Add a sheet called "Log"; place this code in ThisWorkbook module

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


I would use

Environ("UserName")

rather than

Application.UserName

as malcolm did, it is more robust.

Bob Phillips
03-17-2007, 02:02 PM
Add a sheet called "Log"; place this code in ThisWorkbook module

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


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

mdmackillop
03-17-2007, 02:31 PM
Incorporating suggestions
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

Bob Phillips
03-17-2007, 02:41 PM
Look at that, before the thought is out of my head, Malcolm has coded it

lucas
03-17-2007, 02:55 PM
That's pretty slick. I like the log sheet as record.

lucas
03-18-2007, 09:08 AM
for anyone who might be interested....this adds the date column which is handy too:
.Offset(1, 5) = Now