time auto entry
Wrong type of code for what you want.
The For Each will update every cell whenever a calculation takes place.
Try this sheet_change version.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Application.Intersect(Range("a2:a100"), Target) Is Nothing Then
n = Target.Row
If Me.Range("A" & n).Value < "" Then
Me.Range("B" & n).Value = Format(Date, "dd mm yyyy")
Me.Range("C" & n).Value = Format(Time, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
Gord Dibben MS Excel MVP
On Fri, 16 Jan 2009 13:42:05 -0800, Cathy
wrote:
I'm doing something similar and can't quite get it to work. I used your code
and modified for my spreadsheet. I want col B (date) and col C (time) to
auto entry when someone manually enters an item in column A from a list
(name). But, it's still updating with the current date/time instead of
staying static. Any suggestions? Do I not have the code quite right? Thanks
Private Sub Worksheet_Calculate()
Set r = Range("a2:a100")
For Each a In r
If a.Value < "" And a.Offset(0, -10).Value = "" Then
Application.EnableEvents = False
Cells(a.Row, "b").Value = Date
Cells(a.Row, "c").Value = Time
Application.EnableEvents = True
End If
Next
End Sub
|