Track whose made changes

[Kiba]

I was wondering if anyone new a way to create a user prompt that would ask
for user initials when the user opened the workbook. Then save that
information along with the date if they make changes. If they don't make
changes then there's no reason to save that they logged in. I was thinking
this may be a good way to track changes when they forget to update the
revision date field. I haven't started coding this yet. So I appreciate any
and all ideas.

 

[Gary Keramidas]

you can add this to the thisworkbook module and see if it helps

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lastrow).Offset(1, 0) = Environ("Usernme") & " " & Format(Now,
"mm-dd-yyyy")

End Sub

 

[Gary''s Student]

In a standard module, include this single line:
Public firstime As Boolean

In ThisWorkbook code, insert:
Private Sub Workbook_Open()
firstime = True
End Sub


In Worksheet code of any sheet you want to catch changes, insert:
Private Sub Worksheet_Change(ByVal Target As Range)
If firstime = True Then
firstime = False
n = Sheets("changes").Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
Sheets("changes").Cells(n, 1).Value = Now
Sheets("changes").Cells(n, 2).Value = Environ("UserName")
Application.EnableEvents = True
End If
End Sub


This routine uses a special sheet called "changes" to keep the audit
records. The user opens the workbook. The first time a change is made, the
routine records the user's name and date in the "changes" worksheet. The
next time this user or any other user opens the book and makes changes, the
info will be recorded in the next available line.


You must include the "changes" worksheet before inserting the macros.

 

[Kiba]

Is there a way to set it to only keeps track of 10 or so logs autimatically?