automate date entry - update pls.

[medavino]

hi gord.
this was great code but i need range to be a1:aj1 and date to be in ak.
i have over 4000 rows that i need date to always appear in col ak.
how do i change code?
thanks.

medavino

Right-click on the worksheet tab and "View Code"

Copy/paste this code into that sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Any time you make a change in a cell in A1:A10 range, the date/time will appear
in corresponding cell in column B

i am using excel 2003 and need to automatically insert date into a cell when
i update info in a row.
others need to know how current data in a row is.
any thoughts out there?
please keep in mind that although not a total beginner, certain areas in
excel i have never used.
thanks in advance.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:AJ")) Is Nothing Then
With Target
If .Value <> "" Then
Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[medavino]

bob,
I rec'd a compile error msg and highlight of this statement -
" Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy"
i don't know enough about programming to understand.
pls help.
also, i need the date not to update when s/s is calculated or opened. i
thought the Now function would change?
thanks.

 

[Gord Dibben]

As I read you want the date to change in column AK anytime data is changed in a
row from columns A to AJ for 4000 rows.

You sure you want to do this?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:AJ4000")) Is Nothing Then
With Target
If .Value <> "" Then
Me.Range("AK" & Target.Row).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord

 

[Dave Peterson]

There was an unfortunate line break in the post. Try plopping in this portion:

If .Value <> "" Then
Me.Cells(.Row, "AK").Value _
= Format(Now, "dd mmm yyyy hh:mm:ss")
End If

 

[medavino]

YAHOO. i don't understand it but it works !!!
thank you to each of you for your input.
regards
maureen (medavino)