Click Cell for Date and Time in Excel XP

M

MAGICofSeth

As per the instructions, I will get straight to the point.

I would like to have a column of cells from "A9" down to "A(infinity)
that when I click on a single cell in that column, the current date an
time are entered into that cell only. If I were to click on a differen
cell in that column, the current date and time would automatically b
placed into that's cell only. Therby, two different times at leas
would be displated in two different cells which were clicked onl
clicked on a few moments apart.

The date and time need to be unchanging after it is "clicked" in. (no
constantly updating with the current date and time) which is m
problem.

That is all. If people could please email answers or VBA code to me at
[email protected] I would really be grateful.

Thank You!
--Set
 
R

Ron Rosenfeld

As per the instructions, I will get straight to the point.

I would like to have a column of cells from "A9" down to "A(infinity)"
that when I click on a single cell in that column, the current date and
time are entered into that cell only. If I were to click on a different
cell in that column, the current date and time would automatically be
placed into that's cell only. Therby, two different times at least
would be displated in two different cells which were clicked only
clicked on a few moments apart.

The date and time need to be unchanging after it is "clicked" in. (not
constantly updating with the current date and time) which is my
problem.
Click to expand...

Right click on the worksheet tab and select View Code.

Paste the following code into the window that opens and see if it does what you
expect:

===========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
End If

Target.Value = Date + Time
Target.NumberFormat = "d-mmm-yyyy h:mm:ss"
End Sub
===========================

--ron
 
R

Ron Rosenfeld

As per the instructions, I will get straight to the point.

I would like to have a column of cells from "A9" down to "A(infinity)"
that when I click on a single cell in that column, the current date and
time are entered into that cell only. If I were to click on a different
cell in that column, the current date and time would automatically be
placed into that's cell only. Therby, two different times at least
would be displated in two different cells which were clicked only
clicked on a few moments apart.

The date and time need to be unchanging after it is "clicked" in. (not
constantly updating with the current date and time) which is my
problem.

That is all. If people could please email answers or VBA code to me at:
[email protected] I would really be grateful.

Thank You!
--Seth
Click to expand...

Here's another option for code which takes care of a problem in case you select
multiple cells that are not all in Column A:

================================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
End If

For Each c In Target
If c.Column = 1 Then
c.Value = Date + Time
c.NumberFormat = "d-mmm-yyyy h:mm:ss"
End If
Next c
End Sub
==============================


--ron
 
J

JE McGimpsey

You can use the Worksheet_SelectionChange event macro, but I'd recommend
using the _BeforeDoubleClick event instead. The SelectionChange event
will fire when the cell is clicked in, but also when the cell is
activated using Tab, Enter or an arrow key:


Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If .Column = 1 Then
If IsEmpty(.Value) Then
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End If
Cancel = True
End If
End With
End Sub
 
Top