How to auto-enter date when cell is clicked?

[Ron M.]

My spreadsheet is on a shared server, and has several columns of data
entered by various people. These are columns D through L.

In the far right column, I want them to just be able to click on the
cell and that day's date appears. It needs to be static - not
auto-update every time the file is opened. It's in Column L. The top
cell is in Row 4.

When they enter or change data on a row, the date they did it needs to
appear in column L in that row.

The point is to keep their data entry time to an ABSOLUTE minimum. I
know they can select the cell and type control-;, but I'd like to
eliminate that step if at all possible. Each person will enter/change
data several thousand times over the year, so EVERY second I can shave
off the process helps.

Clicking in the cell to make the date appear would work fine, unless
there's a better approach out there.

Thanks very much,
Ron M.

 

[Dave Peterson]

Clicking on the cell won't work, but making a typing change could do it.

J.E. McGimpsey shows how at:
http://www.mcgimpsey.com/excel/timestamp.html

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 12 And .Row >= 4 Then
.Value = Format(Date, "dd mmm yyyy")
End If
End With

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

RP
(remove nothere from the email address if mailing direct)

 

[Ron M.]

Bob: that works, but some people have trouble with it. They click on
the cell and the date appears, true, but then if they hit return or
enter or the down or up arrow, it puts the date in THAT cell, too. If a
date was previously entered in that cell, it replaces it with the
current one.

I dunno, I think we'll just have to go with control-; .

Thanks,
Ron M.

 

[Bob Phillips]

Do you want to check the entry, and if already present as a date, don't
overwrite? If so


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 12 And .Row >= 4 Then
If Not IsDate(.Value) Then
.Value = Format(Date, "dd mmm yyyy")
End If
End If
End With

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

RP
(remove nothere from the email address if mailing direct)