Date Functions‎ > ‎

User Timestamps

PROBLEM:                                                                 Sample File:   UserTimeStamps.xls
"I want to have timestamps in my workbook showing who/when the workbook was last edited.  That would be a separate date/timestamp for any user who uses the workbook."
 
SPECIFICATIONS:
  1. Any number of users
  2. Should add users automatically
  3. Each user's date/timestamp only gets added/updated when a change is made in the workbook, not just from viewing it.
METHOD:
  1. A separate sheet called LOG must exist in the workbook.  The macro does the rest.
  2. The macro below goes into the ThisWorkbook module and watches for activity anywhere in the workbook except the Log sheet.
  3. If an edit is detected, the users Windows Login name is detected and updated/added on the Log sheet.

Code

Option Explicit

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'Author:    Jerry Beaucaire
'Date:      8/8/2012
'Summary:   Maintains a date/timestamp for last edit for every user who edits this workbook
'           Adds usernames and stamps automatically
Dim user As Range

With Sheets("Log")
    If sh.Name <> .Name Then
        On Error Resume Next
        Set user = .Range("A:A").Find(Environ("UserName"), LookIn:=xlValue, LookAt:=xlWhole)
        If Not user Is Nothing Then
            user.Offset(, 1) = Now
        Else
            With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
                .Value = Environ("UserName")
                .Offset(, 1) = Now
            End With
            .Columns.AutoFit
        End If
    End If
End With

End Sub


Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!

Comments