Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Username & Date/Time Stamp
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Username & Date/Time Stamp
On 18 Mar 2006 15:54:16 -0800, "Ken D" wrote:
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 --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Username & Date/Time Stamp
Be aware though that this is the APPLICATION username, not the one
logged on to Windows. Hans |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Username & Date/Time Stamp
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. In article .com, "Ken D" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Username & Date/Time Stamp
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 A1:D4 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 In article , Hank Scorpio wrote: On 18 Mar 2006 15:54:16 -0800, "Ken D" wrote: 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 --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Including Environ Username in Cell | Excel Worksheet Functions | |||
date/time stamp | Excel Worksheet Functions | |||
Date/Time stamp with one stroke? | Excel Discussion (Misc queries) | |||
date/time | Excel Worksheet Functions | |||
bringing data from one workbook to another using a date stamp func | Excel Worksheet Functions |