Username & Date/Time Stamp

[Ken D]

I've created the following code to record Username and a Date/Time
Stamp in cells K1 & L1 (respectively) after a user makes any change to
a cell in the same row (A1 through J1).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1:J1"), Target) Is Nothing Then Exit Sub
Range("K1").Formula = Format(User())
Range("L1").Formula = Format(Now(), "dd-mmm-yyyy hh:mm:ss am/pm")
End Sub

I need to repeat this code for approx 50 rows. Any advice on the most
efficient way to handle this is greatly appreciated.

Thanks,
Ken

 

[Hank Scorpio]

I've created the following code to record Username and a Date/Time
Stamp in cells K1 & L1 (respectively) after a user makes any change to
a cell in the same row (A1 through J1).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1:J1"), Target) Is Nothing Then Exit Sub
Range("K1").Formula = Format(User())
Range("L1").Formula = Format(Now(), "dd-mmm-yyyy hh:mm:ss am/pm")
End Sub

I need to repeat this code for approx 50 rows. Any advice on the most
efficient way to handle this is greatly appreciated.

If I understand you correctly, you could modify something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row > 50 Or Target.Column > 10 Then Exit Sub

Cells(Target.Row, 11).Formula = Format(Application.UserName)

Cells(Target.Row, 12).Formula = Format(Now(), "dd-mmm-yyyy
hh:mm:ss am/pm")

End Sub

 

[flummi]

Be aware though that this is the APPLICATION username, not the one
logged on to Windows.

Hans

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/timestamp.html

One could modify one of the macros found there something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:J50"), .Cells) Is Nothing Then
Application.EnableEvents = False
Cells(.Row, 11).Value = Application.UserName
With Cells(.Row, 12)
.NumberFormat = "dd-mmm-yyyy hh:mm:ss am/pm"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

Note that using Format(User()) does nothing except make an extra
function call, and that

Range("L1").Formula = Format(Now(), "dd-mmm-yyyy hh:mm:ss am/pm")

WIll not affect how the date/time is displayed - only the cell's
..NumberFormat property determines that. Using Format just makes the
date/time a string function which is then interpreted by XL's parser and
displayed according to the cell's number format.

 

[JE McGimpsey]

One caveat - By checking the .Row or .Column property of Target, this is
vulnerable to a couple of errors if the selection includes multiple
cells.

For instance, if A14 is selected, with C3 the active cell, then when a
change is made in C3, the macro will place the timestamp in J1:K1, since
Target returns the *selection* when the change occurred, not the cell
that was changed, and the .Row and .Column properties will return the
row and column of the first cell in the selection.

Likewise, if J50:Z2000 were selected, and Y1400 were changed, a
timestamp would be entered in K50:L50.

One way to deal with this is to abort the macro if a multiple selection
is passed:

If Target.Cells.Count > 1 Then Exit Sub

Also, realize that changing the Value (or Formula) of a cell will
recursively call Worksheet_Change(). It's usually better to turn off
events prior to changing the cell's value:

Application.EnableEvents = False
Cells(Target.Row, 11).Value = Application.UserName
With Cells(Target.Row, 12)
.NumberFormat = "dd-mmm-yyyy hh:mm:ss am/pm"
.Value = Now
End With
Application.EnableEvents = True