Tracking Users Who Change a Worksheet

[smlaff01]

Hi, I have come across a great piece of code on the board that was created by Myles. I tried to reply to that post (from 2005) but it didn't seem to work. I am pretty much a VBA rookie, so I am looking for some advice in trying to modify the code

What I would like to do is create a pop up box (or something similar) in which a user can input their actual name and then this name would be copied into the running change log. The way the code is presently set-up it copies the usrename into this log, but I have multiple users accessing the same computer, so this username is not helping me out.

Also would it be possible to copy this log to a new worksheet (hidden worksheet) instead of on the same page?

Here is the code created by Myles:

Private Sub Worksheet_Change (ByVal Target as Excel.Range)
Dim V As Long
Application.EnableEvents = False

Set rng1 = Application.Union (Range ("a1: g1"), Range ("H: IV"))
Set rng = Application.Intersect (Target, rng1)
If Not rng is nothing then Exit Sub

V = Target.Offset (0, 12). Value
If Target.Offset (0, 12) = "" Then
With Range ("H" & Target.Row)
. Value = Target.Address & ": first entry by " & Application.UserName & " at " & Now ()
. ColumnWidth = 60
. Interior.ColorIndex = 33
End With
Target.Offset (0, 12). Value = Target.Value
Application.EnableEvents = True
Exit Sub
End If
Target.Offset(0, 12).Value = Target.Value
With Range("H" & Target.Row)
.Value = Target.Address & " changed from " & V & " to " & Target.Value & " by " & Application.UserName & " at " & Now()
.ColumnWidth = 60
.Interior.Color = vbYellow
End With
Application.EnableEvents = True
End Sub

Please view the attachment to see how this code is working.

[Rick_Stanich]

This should get you started. I added a CommandButton for login, it will retain the last login in the registry even after closing the file. the user will need to login PRIOR to making changes.

Please Login or Register  to view this content.