Log Sheet

[Shane]

I am trying to create a sheet that tracks the items entered into a certain
cell, or group of cells. What is the simplest code to accomplish this?

Ex:
I have a sheet in workbook "File" named "Log" and another sheet in the same
workbook named "Agenda". I plan on using "Agenda" multiple times, deleting
and re-entering information in the source cells, but I would like "Log" to
keep a list of everything entered in "Agenda".

Thanks!

 

[Gord Dibben]

Log sheet A1:C1 has titles of Change Made, Cell Address, Time Changed

Copy this code to sheet module of Agenda.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:G10"
Dim ocell As Range
Dim wslog As Worksheet
Dim lRow As Long
Set wslog = Sheets("Log")
lRow = wslog.Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each ocell In Target
If ocell.Value <> "" Then
ocell.Copy Destination:=wslog.Range("A" & lRow + 1)
With wslog.Range("B" & lRow + 1)
.Value = ocell.Address
.Offset(0, 1).Value = Format(Now, "hh:mm:ss")
End With
End If
Next ocell
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP