PDA

View Full Version : Update Date



suresh
03-20-2006, 05:19 PM
Hi Team:hi:,
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 :help.
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 :banghead:


Thanks in advance,
Suresh.

mdmackillop
03-20-2006, 06:06 PM
Hi Suresh,
Paste the following in the Nodes worksheet module
Regards
MD


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

suresh
03-21-2006, 08:44 AM
Hi MD,
It is just working great. You guys are wonderful. Thanks somuch for your help.:beerchug:

Suresh.

lucas
03-22-2006, 08:48 AM
Another KB entry Malcolm?!
Perfect for the short line entries if we had one.

mdmackillop
03-22-2006, 10:36 AM
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
03-22-2006, 10:44 AM
I'm already using it and I'm sure others would find it useful. Thanks

mdmackillop
03-22-2006, 02:38 PM
KB Item (Thanks Steve)
http://vbaexpress.com/kb/getarticle.php?kb_id=863

lucas
03-23-2006, 10:35 AM
Any way to get this to ignore row 1 or the header row?


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

mdmackillop
03-23-2006, 12:34 PM
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

lucas
03-23-2006, 01:17 PM
Thanks Malcolm that works great

suresh
03-23-2006, 02:41 PM
Hi MD, Steve and other team,
Thanks for getting this done. Now it is perfectly working for me. :thumb
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:help .

Thanks,:beerchug:
Suresh.

mdmackillop
03-23-2006, 03:30 PM
Hi Suresh,
You should post this as a new question for a wider response.
Regards
MD

Cyberdude
03-25-2006, 05:38 PM
Cells(Target.Row, "P") = Int(Now()) Malcolm, is there a reason you chose Int(Now()) instead of =Date??

mdmackillop
03-25-2006, 06:06 PM
Hi Sid
No good reason. Whatever works, but I agree Date is better!
Regards
Malcolm

jindon
03-25-2006, 06:21 PM
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....

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