Resolved >>> How To: List Recent Changes to Document?

**joshjoneswas** · 10-22-2007, 09:59 AM

Hi Everyone,

I am in need of something that I cannot find anywhere on the net nor could I hack around and figure out on my own. Is there a way to list the recent changes to an excel document on the document itself?

For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:

B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm
A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm

I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.

Anyone have any ideas on this one?

Thanks!

**Myles** · 10-24-2007, 01:02 AM

Hi jos,

I have two ways to go about it.

Take #1
Make a copy of your entries (as you type) into another range - discreetly set apart. and set the scrollarea to “hide” . (Better still, you can copy to a hidden worksheet).

The following Worksheet Change event will keep track as you desire (with some bells and whistles!).

Private Sub Worksheet_Change (ByVal Target as Excel.Range)
Dim V As Long
Application.EnableEvents = False

Set rng1 = Application.Union (Range ("a1: g1"), Range ("H: IV"))
Set rng = Application.Intersect (Target, rng1)
If Not rng is nothing then Exit Sub

V = Target.Offset (0, 12). Value
If Target.Offset (0, 12) = "" Then
    With Range ("H" & Target.Row)
       . Value = Target.Address & ": first entry by " & Application.UserName & " at " & Now ()
       . ColumnWidth = 60
       . Interior.ColorIndex = 33
    End With
    Target.Offset (0, 12). Value = Target.Value
    Application.EnableEvents = True
    Exit Sub
End If
Target.Offset(0, 12).Value = Target.Value
With Range("H" & Target.Row)
  .Value = Target.Address & " changed from " & V & " to " & Target.Value & " by " & Application.UserName & " at " & Now()
   .ColumnWidth = 60
  .Interior.Color = vbYellow
End With
Application.EnableEvents = True
End Sub

Rem: place in WorkBook module:

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A:I"
End Sub

Take#2

Alternately, you could copy your entries (again as you type) into “Cell Comments”. Then deploy the following WorkSheet Change event to keep tabs of the record history

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim X As String
Dim C As String

Application.EnableEvents = False

Set rng = Application.Intersect(Target, Range("a2:g65536"))

If Not rng Is Nothing Then
On Error Resume Next
 C = Target.Comment.Text
 If C = "" Then C = "<blank>"
Target.ClearComments

    X = Target.Value
    With Target
      .AddComment.Text X
      .Comment.Visible = False
      .Interior.Color = vbRed
      .Font.Color = vbWhite
    End With

        With Range("H" & Target.Row)
            .Value = Target.Address & ": Old value " & C & " changed to " & Target.Value & " by " & Application.UserName & " at " & Now()
            .Columns.AutoFit
            .Interior.ColorIndex = 33
        End With
End If
'Optional line of code
If C = "<blank>" Then Range("H" & Target.Row).Clear
Application.EnableEvents = True

End Sub

I would personally prefer Take #1 to Take #2, if because it is tidier.

See Attachments. Amend ranges to suit.

**joshjoneswas** · 10-24-2007, 10:21 AM

Hey that #1 option was great.

I amended it to keep a running change down a hidden column on another worksheet rather than changing each rows change cell to the right of it.

Now it will track changes indefinetly all the way down.

Thank you a TON for your help. I'm a VB guy but new to VBA and this was a HUGE help!

**Myles** · 10-25-2007, 02:05 AM

Jos,

My pleasure it works for you. I lost sight of the running change bit.

As you might have noticed, when an entry is text and not numeric, the declaration of V as Long (as I have it) will pose problems. Amend this declaration to: Dim V as Variant