Current time Current date.

[yo beee]

TIA to anyone who can help with this macro. I need a macro in EXCEL to enter
the current time in a cell when another cell has data entered. For instance,
if I add a number in cel B1 and select the enter button, cell A1 will show
the computer's current time in which that data was entered. I also need a
macro to do the very same thing with the date.
Thanks again,
yo beee

 

[Paul B]

yo beee, right click your worksheet tab and view code, paste this in the
window that opens

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put the time in column A and the date in column C
' date and time in column D when you put data in column B
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
Target.Offset(0, -1).Value = Time()
Target.Offset(0, 1).Value = Date
Target.Offset(0, 2).Value = Now
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Vasant Nanavati]

Put the following macro in the worksheet's code module:

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A1") = Time 'Date
End If
End Sub

 

[yo beee]

Paul,

Thanks for the help and the code worked fine. Now, I just need to tweak it a
bit. I thought I could figure it out on my own but I am at a loss. So, here
is specifically what I need to do. At cell C9, I will add a line of text and
hit enter. I need the current date to be entered into A9 and the time to be
entered into B9. A few moments later I will add a line of text into C10, and
I need the exact date to be filled into cell A10 and the time into B10, and
so on and so on, and so. But I only need this to start on line 9 and down
from there. Hopefully, you can help me.

TIA,

yo beee

 

[Norman Jones]

Hi Yo Beee

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 3 And .Row > 8 Then
.Offset(0, -1).Value = Time()
.Offset(0, -2).Value = Date
End If
End With
End Sub

 

[JE McGimpsey]

See

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

You could use something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C:C"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, -2).Resize(1, 2)
If IsEmpty(Target.Value) Then
.ClearContents
Else
.Item(1).NumberFormat = "dd mmm yyyy"
.Item(1).Value = Date
.Item(2).NumberFormat = "hh:mm:ss"
.Item(2).Value = Time
End If
End With
Application.EnableEvents = True
End If
End With
End Sub

 

[yo beee]

It worked like a charm. Thanks a mil'. Genius, you are!!! Kind, as well, to
help someone out. It is much appreciated.
yo beee