Automatic Date insertions

[Ducttape19]

I would like to be able to have a date inserted into a cell when another cell is modified. Let's say that cell E1 changes, I would like the date that E1 changed on to appear in F2 automatically. Is this at all possible or will I have to keep typing in the date by hand? Thank.

 

[Juan Pablo González]

You have to use the Change() event of the worksheet, like this:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$1" Then
Application.EnableEvents = False
Range("F2").Value = Date
Application.EnableEvents = True
End If
End Sub </pre>

 

[Juan Pablo González]

Another option.

In F2 use this formula and format as time (Or date and time, or whatever you want !)

=SI(E1,STATICNOW(),"")

and paste this code in a standard module
<pre>Function STATICNOW() As Variant
STATICNOW = Now
End Function</pre>
That way, whenever E1 changes, the formula in F2 will recalculate.

 

[Ducttape19]

When I try and use the STATICNOW() function I get a #value error. Just STATICNOW() works fine. I don't know???