Preserving Auto Date
Posted by Rob on April 24, 2001 10:45 PM
I'm trying to set up a simple "Comment Log" which automatically inputs the current date and time into one cell, when a comment is put into an adjacent one. the formula I'm trying to use is "=IF(B2="","",NOW())". It works, but the problem is it re-evaluates NOW() everytime any cell on the worksheet is changed. Is there a simple way to preserve this time so that it gets input once, then stays the same?
Any help would be appreciated, thanks.
Posted by Dave Hawley on April 25, 2001 12:19 AM
Hi Rob
There are two way to handle this.
1. Change your formula to this:
=IF(B2="","",IF(A1=NOW(),A1,NOW())"
This assumes the cell housing the formula is in A1. It will create a circular reference which you can ignore.
2. The prefered method.
Right click on your sheet name tab and select "View Code", paste in this code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
''''''''''''''''''''''''''''''''''''
'Places the current date and time in cell A1 _
If B1 <> ""
'''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
If Target <> "" Then
Range("A1") = Now
Range("A1").EntireColumn.AutoFit
End If
End If
End Sub
Again this assumes you want the Now in cell A1, change this to suit. Then Push Alt+Q.
Dave
OzGrid Business Applications
