Displaying the time and date

  • Thread starter Thread starter FallouX
  • Start date Start date
F

FallouX

I need some help on displaying the time and date when a cell is enter.
example when i enter a number in cell a1 i want to display the time and
date of which cell a1 is enter.
 
Hi FallouX!

You'll need a Worksheet_Change event handling subroutine:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then ' Entry in column A
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
End With
End Sub

This goes in the Sheet Module and in this case it looks at column A.

The subroutine allows the entries in column A to be changed and then
updates the date time in column B.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,

What does de sub look like if date and time of the input in a certain cell
should be written in the comment of that cell ? Could be handy, by hoovering
over the cell you see the date and time of the most recent change of the
content of the cell. That now brings me to the point that it will be
necessary to check wether the content of the cell was really changed because
it seems not desirable to have a new date in the comment if the cell was
only "double clicked" but was left without any change of its content (as is
the case very often).

Jack Sons
The Netherlands
 
Hi Jack!

Here's a sub edited from an original by Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/ccomment.htm

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
Dim ccc As String
ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
& " " & Target.Value ' -- Application.UserName
If Target.Comment Is Nothing Then
Target.AddComment.Text ccc
Else
Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
End If
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

This adds a comment with date and time and makes further additions if
the cell is subsequently changed.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,

I tried it, it worked.

But I also got a new entry in the comment when I double clicked the cell
and then left it unchanged. What code is needed to check (when exiting the
cell) wether its contents was changed or not. I would like a new time etc.
registered in the comment only if there was a change of its content. Is that
possible? If so, please show the code.

Jack.
 
Back
Top