Track users who use excel file

[ShieBoon]

Hello, do you guys know of any code that can allow me to track the users who use a shared excel file and log the records in a worksheet within the file?

i'm using xl 2003

 

[VoG]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=381604

 

[ShieBoon]

Hey vog, that has helped. Thanks for the reply too. Anw i have a question regarding the codes.

Dim LR As Long
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & LR + 1).Value = Now
.Range("B" & LR + 1).Value = Sh.Name
.Range("C" & LR + 1).Value = Target.Address(False, False)
.Range("D" & LR + 1).Value = Target.Value
End With
Application.EnableEvents = True

Why do we need to disable the events?

Thanks,
Shie Boon

 

[VoG]

It is just to ensure that the event code is not called needlessly when the macro writes to (changes) the Log sheet.

 

[Damo10]

Hi VoG,

I would like to use tihs code in one of my reports, is it possible to exclude certain cells from being included? I have a range called "Data" that I would like to exclude.

Also is it possible for the previous contents before the change to be included? so there is a before and after.

Regards Damian

 

[VoG]

Hi VoG,

I would like to use tihs code in one of my reports, is it possible to exclude certain cells from being included? I have a range called "Data" that I would like to exclude.

Also is it possible for the previous contents before the change to be included? so there is a before and after.

Regards Damian

Can you post the code that you want to use - there are several versions in that thread.

 

[Damo10]

Hi,

Here is the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = VBA.Environ("username")
    .Range("B" & LR + 1).Value = Now
    .Range("C" & LR + 1).Value = Sh.Name
    .Range("D" & LR + 1).Value = Target.Address(False, False)
    .Range("E" & LR + 1).Value = Target.Value
End With
Application.EnableEvents = True
End Sub

Regards

 

[VoG]

Try (untested)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long, NewVal As Variant, OldVal As Variant
If Sh.Name = "Log" Then Exit Sub
If Not Intersect(Target, Range("Data")) Is Nothing Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
With Sheets("Log")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = VBA.Environ("username") 'user
    .Range("B" & LR + 1).Value = Now 'date and time
    .Range("C" & LR + 1).Value = Sh.Name 'sheet
    .Range("D" & LR + 1).Value = Target.Address(False, False) 'cell
    .Range("E" & LR + 1).Value = OldVal 'previous value
    .Range("F" & LR + 1).Value = Target.Value 'new value
End With
Application.EnableEvents = True
End Sub

 

[Damo10]

Hi,

Tried it and it is not doing anything, nothing is being entered onto the Log sheet.

Regards

 

[VoG]

Now tested and working. Make sure that macros are enabled and that Events are enabled. For Events, in the code window press CTRL + G, type in

Application.EnableEvents=True

and press Enter.

Then try again.