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
'==================================================================