date/time stamp
 

I know I probably did post my reply correctly to get a possible solution to
my problem. Any further help would be greatly appreciated.


Sorry, but I failed to mention the process correctly. If end user enters
data in any cell in column A beginning at cell a2, then I would like the
corresponding cell(same row) in column B to have a date/time stamp that would
not change when the date/time changes or when the file is saved and reopened.
Example: if user enters data in cell A2, then B2 would automatically display
date/time stamp. If user enters data in cell A3, then B3 would have a
date/time stamp...and so on..and so on. TIA.

-----------------------------------------------

Enter this code in the module of the concerned sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Now()
End If
End Sub

Will change the date only when you change or enter value in A1
Mangesh

________________________________________

I'm using Excel 2003.

Is this possible?

If end user enters data in A1, can I add a date/time function that will
automatically be entered in cell B1, but not update when the date/time
changes or when the file is closed/opened? Using Today() or Now(), the
date/time in B1 updates when file is closed and then opened.

TIA

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
.Offset(0, 1).Value = Time
.Offset(0, 1).NumberFormat = "hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Jan" wrote in message
...
I know I probably did post my reply correctly to get a possible solution
to
my problem. Any further help would be greatly appreciated.


Sorry, but I failed to mention the process correctly. If end user enters
data in any cell in column A beginning at cell a2, then I would like the
corresponding cell(same row) in column B to have a date/time stamp that
would
not change when the date/time changes or when the file is saved and
reopened.
Example: if user enters data in cell A2, then B2 would automatically
display
date/time stamp. If user enters data in cell A3, then B3 would have a
date/time stamp...and so on..and so on. TIA.

-----------------------------------------------

Enter this code in the module of the concerned sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Now()
End If
End Sub

Will change the date only when you change or enter value in A1
Mangesh

________________________________________

I'm using Excel 2003.

Is this possible?

If end user enters data in A1, can I add a date/time function that will
automatically be entered in cell B1, but not update when the date/time
changes or when the file is closed/opened? Using Today() or Now(), the
date/time in B1 updates when file is closed and then opened.

TIA

Bob,

I tried to adapt the number format so that date and time display because I
need to have both. I have replaced your " "hh:mm:ss" with ""mm/dd/yy h:mm
AM/PM;@".
However, no matter what format I try to use the date displays as 01/0/1900.
Do you have any suggestions to the the date display as the current date?


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
.Offset(0, 1).Value = Time
.Offset(0, 1).NumberFormat = "hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Jan" wrote in message
...
I know I probably did post my reply correctly to get a possible solution
to
my problem. Any further help would be greatly appreciated.


Sorry, but I failed to mention the process correctly. If end user enters
data in any cell in column A beginning at cell a2, then I would like the
corresponding cell(same row) in column B to have a date/time stamp that
would
not change when the date/time changes or when the file is saved and
reopened.
Example: if user enters data in cell A2, then B2 would automatically
display
date/time stamp. If user enters data in cell A3, then B3 would have a
date/time stamp...and so on..and so on. TIA.

-----------------------------------------------

Enter this code in the module of the concerned sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Now()
End If
End Sub

Will change the date only when you change or enter value in A1
Mangesh

________________________________________

I'm using Excel 2003.

Is this possible?

If end user enters data in A1, can I add a date/time function that will
automatically be entered in cell B1, but not update when the date/time
changes or when the file is closed/opened? Using Today() or Now(), the
date/time in B1 updates when file is closed and then opened.

TIA

First, note that the macro as written will give undesired results if a
multiple selection includes more than one column, including column A.
For instance, if A1:J10 is selected with C5 active, and C5 is changed by
the user, all the cells in B1:K10 will be overwritten with the date
stamp. One way to handle that would be to abort if there's a multiple
selection.

Second, Time only returns the time. For dates as well, use Now instead.

I might amend the macro like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rSelect As Range
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Me.Range("A:A")) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd/mm/yy hh:mm AM/PM"
End With
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub



In article ,
Jan wrote:

I tried to adapt the number format so that date and time display because I
need to have both. I have replaced your " "hh:mm:ss" with ""mm/dd/yy h:mm
AM/PM;@".
However, no matter what format I try to use the date displays as 01/0/1900.
Do you have any suggestions to the the date display as the current date?

Oops. Delete the Dim rSelect As Range line (it was from a different
worksheet_change procedure that I use).

In article ,
JE McGimpsey wrote:


I might amend the macro like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rSelect As Range

Is there a way to make it so that its "if the value of the cell changes"
not "if its double clicked"??

Also, I would like to protect and hide those cells in the worksheet and
not allow the users to edit or even see those date/times. Problem is
when I protect the cells and they enter the data in cell a1 or
whatever, it doesnt change the date/time cause well.... they're not
allowed to change it so it doesn't do the change.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=380369

Take a look he

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


In article ,
DKY wrote:


Is there a way to make it so that its "if the value of the cell changes"
not "if its double clicked"??

Also, I would like to protect and hide those cells in the worksheet and
not allow the users to edit or even see those date/times. Problem is
when I protect the cells and they enter the data in cell a1 or
whatever, it doesnt change the date/time cause well.... they're not
allowed to change it so it doesn't do the change.

Okay, so here's my code then (that I stole from this site and revised to
fit my needs).


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Me.Range("E:E")) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 2)
.Value = Now
.NumberFormat = "mmm dd yyyy hh:mm:ss AM/PM"
End With
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
--------------------


How would I go about adding the windows log-on username to this? I
found this

http://blogs.officezealot.com/charle...2/10/3574.aspx

Which says you can use this


Code:
--------------------
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
--------------------


But where do I put the function in the code and how do I call it to
use?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=380369