track changes

[BBB]

hi,

does anyone know if it is possible to write a marco to track the changes in
an excel document? if so do you know where i could find some information on
this or help me write this.

i want to create a function button that will create a new page to display
all the changes made to a document in say the last month. i am having
difficulties making this work as i can not write macros in a shared
workbook...

thanks,

 

[JE McGimpsey]

Instead of macros, try using change history (see "Turn on the change
history for a workbook" in XL Help).

 

[BBB]

JE,

i can manually open the track changes function and create a history page,
but this wil be used by people that have limited experience with excel. i
wanted to make a function button that would generate the history list
automatically if possible.

 

[ryguy7272]

Right-click on the tab and copy/paste this code into the window that opens:
Function MyUserName() As String
MyUserName = Environ("UserName")
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
..Select
..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = MyUserName()
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub

An alternative:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

One more, for good measure (login name is entered into Column Z)
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("a1:iv65536")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "Z").Value = Environ("username")
Application.EnableEvents = True
End Sub


Regards,
Ryan---