software

I've created the following code to record Username and a Date/Time
Stamp in cells K1 amp; 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(quot;A1:J1quot;), Target) Is Nothing Then Exit Sub
Range(quot;K1quot;).Formula = Format(User())
Range(quot;L1quot;).Formula = Format(Now(), quot;dd-mmm-yyyy hh:mm:ss am/pmquot;)
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,
KenOn 18 Mar 2006 15:54:16 -0800, quot;Ken Dquot; gt; wrote:

gt;I've created the following code to record Username and a Date/Time
gt;Stamp in cells K1 amp; L1 (respectively) after a user makes any change to
gt;a cell in the same row (A1 through J1).
gt;
gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt; If Intersect(Range(quot;A1:J1quot;), Target) Is Nothing Then Exit Sub
gt; Range(quot;K1quot;).Formula = Format(User())
gt; Range(quot;L1quot;).Formula = Format(Now(), quot;dd-mmm-yyyy hh:mm:ss am/pmquot;)
gt;End Sub
gt;
gt;I need to repeat this code for approx 50 rows. Any advice on the most
gt;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 gt; 50 Or Target.Column gt; 10 Then Exit Sub

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

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

End Sub

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

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

HansSee

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 gt; 1 Then Exit Sub
If Not Intersect(Range(quot;A1:J50quot;), .Cells) Is Nothing Then
Application.EnableEvents = False
Cells(.Row, 11).Value = Application.UserName
With Cells(.Row, 12)
.NumberFormat = quot;dd-mmm-yyyy hh:mm:ss am/pmquot;
.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(quot;L1quot;).Formula = Format(Now(), quot;dd-mmm-yyyy hh:mm:ss am/pmquot;)

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.In article .comgt;,
quot;Ken Dquot; gt; wrote:

gt; I've created the following code to record Username and a Date/Time
gt; Stamp in cells K1 amp; L1 (respectively) after a user makes any change to
gt; a cell in the same row (A1 through J1).
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; If Intersect(Range(quot;A1:J1quot;), Target) Is Nothing Then Exit Sub
gt; Range(quot;K1quot;).Formula = Format(User())
gt; Range(quot;L1quot;).Formula = Format(Now(), quot;dd-mmm-yyyy hh:mm:ss am/pmquot;)
gt; End Sub
gt;
gt; I need to repeat this code for approx 50 rows. Any advice on the most
gt; efficient way to handle this is greatly appreciated.
gt;
gt; Thanks,
gt; Ken

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 gt; 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 = quot;dd-mmm-yyyy hh:mm:ss am/pmquot;
.Value = Now
End With
Application.EnableEvents = True

In article gt;,
Hank Scorpio gt; wrote:

gt; On 18 Mar 2006 15:54:16 -0800, quot;Ken Dquot; gt; wrote:
gt;
gt; gt;I've created the following code to record Username and a Date/Time
gt; gt;Stamp in cells K1 amp; L1 (respectively) after a user makes any change to
gt; gt;a cell in the same row (A1 through J1).
gt; gt;
gt; gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; If Intersect(Range(quot;A1:J1quot;), Target) Is Nothing Then Exit Sub
gt; gt; Range(quot;K1quot;).Formula = Format(User())
gt; gt; Range(quot;L1quot;).Formula = Format(Now(), quot;dd-mmm-yyyy hh:mm:ss am/pmquot;)
gt; gt;End Sub
gt; gt;
gt; gt;I need to repeat this code for approx 50 rows. Any advice on the most
gt; gt;efficient way to handle this is greatly appreciated.
gt;
gt; If I understand you correctly, you could modify something like this:
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;
gt; If Target.Row gt; 50 Or Target.Column gt; 10 Then Exit Sub
gt;
gt; Cells(Target.Row, 11).Formula = Format(Application.UserName)
gt;
gt; Cells(Target.Row, 12).Formula = Format(Now(), quot;dd-mmm-yyyy
gt; hh:mm:ss am/pmquot;)
gt;
gt; End Sub
gt;
gt; ---------------------------------------------------------
gt; Hank Scorpio
gt; scorpionet who hates spam is at iprimus.com.au (You know what to do.)
gt; * Please keep all replies in this Newsgroup. Thanks! *