Update Date

[suresh]

Hi Team,
Ever since I came here you guys have been nothing but helpful and I am very grateful for all your help and greatful of having such a great people in this forum.

Here I have comeup with another request .
Here I am attaching the spreadsheet.
Could you please help me to update the date field ("Last Update" field in Nodes worksheet).

When ever we modify/change any records in this worksheet, automatically the current date should be updated in "last update" field. Any ideas


Thanks in advance,
Suresh.

[mdmackillop]

Hi Suresh,
Paste the following in the Nodes worksheet module
Regards
MD

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, "P") = Int(Now())
Application.EnableEvents = True
End Sub

[/vba]

[suresh]

Hi MD,
It is just working great. You guys are wonderful. Thanks somuch for your help.

Suresh.

[lucas]

Another KB entry Malcolm?!
Perfect for the short line entries if we had one.

[mdmackillop]

Good idea Steve.
I think though, as it's a worksheet event, it would need some instruction for the Newbie, so maybe not a short line one in this case.
Regards
Malcolm

[lucas]

I'm already using it and I'm sure others would find it useful. Thanks

[mdmackillop]

KB Item (Thanks Steve)
http://vbaexpress.com/kb/getarticle.php?kb_id=863

[lucas]

Any way to get this to ignore row 1 or the header row?

[VBA]
Cells(Target.Row, "P") = Int(Now())
[/VBA]

[mdmackillop]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "B") = Int(Now())
Application.EnableEvents = True
End Sub
[/VBA]

[lucas]

Thanks Malcolm that works great

[suresh]

Hi MD, Steve and other team,
Thanks for getting this done. Now it is perfectly working for me.
Is there a way to send email (Through Lotus notes) to late clients (Compare the date field and if the date is >180 days, send email to them)??
I have found few kb entries in our forum. Need to try them. If you have any ideas please share with me .

Thanks,
Suresh.

[mdmackillop]

Hi Suresh,
You should post this as a new question for a wider response.
Regards
MD

[Cyberdude]

Cells(Target.Row, "P") = Int(Now())

Malcolm, is there a reason you chose Int(Now()) instead of =Date??

[mdmackillop]

Hi Sid
No good reason. Whatever works, but I agree Date is better!
Regards
Malcolm

[jindon]

Hi,
the following code doesn't update if the change made is the same as before the change
and also no update for just delete key on the empty cell....
[vba]
Private OldData As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row = 1 Then Exit Sub
If .Value = OldData Then Exit Sub
Application.EnableEvents = False
Range("p" & .Row) = Date
Application.EnableEvents = True
End With
End Sub
[/vba]