Event Log

[Brabed]

I am looking to have record of when the values of certain cells change so that I could go back and reference. I am thinking of having one worksheet show all the cell changes on another worksheet. How do I do this? Thanks in advance.....

 

[BrianB]

Here is some code. Right click the sheet tab and View code - copy & paste it there. You need another sheet called "Sheet2" for the log.

I have not tested this in use (Don't know that I would want to )
I have included code to get the user login name because experience shows that Application.UserName is not always accurate. It comes from Tools/Options which is not always updated.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Dim LogSheet As Worksheet
Dim LogRow As Long
Dim OldValue As Variant
Dim NewValue As Variant
Dim User As String
'====================================================================

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Set LogSheet = Worksheets("Sheet2")
    LogRow = LogSheet.Range("A65536").End(xlUp).Row + 1
    NewValue = Target.Value
    '----------------------------------------------------
    '- enter log details
    LogSheet.Cells(LogRow, "A").Value = Now             ' date
    LogSheet.Cells(LogRow, "B").Value = MyUserName      ' user
    LogSheet.Cells(LogRow, "C").Value = Target.Address  ' cell ref
    LogSheet.Cells(LogRow, "D").Value = OldValue
    LogSheet.Cells(LogRow, "E").Value = NewValue
End Sub
'==================================================================
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    OldValue = ActiveCell.Value
End Sub

'==================================================================
'- function to get server login name
'- or Application.Username if not logged in
'==================================================================
Private Function MyUserName() As String
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        MyUserName = Left$(strUserName, lngLen - 1)
    Else
        MyUserName = Application.UserName
    End If
End Function
'==================================================================