How can I use the NOW function and keep it from auto updating?

[Guest]

I have created a request for information form in Excel and want Excel to
record the date and time the request is made. I used an IF function to keep
the cell empty unless information is place in it and the NOW function to
record the date/time if a request is made. The problem is, everytime someone
enters a new request for information, every date and time field on the form
is updated automatically with the date and time at that time. How can I make
this work? I appreciate any assistance to this end.

 

[Guest]

one of the ways to do it is to use a user defined function

function currentdate(ref)
if ref = "" then xx="" else xx=now
currentdate = xx
end function

enter the function in a module in your workbook
and use = currentdate(B2)
for the cell you want to show the date when something is entered in B2
change the cell reference as you want to .

 

[Gord Dibben]

Bear

You could use event code to enter the date which would remain static.

Below are a couple of sets of event code. Use whichever suits you.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Col B time will change if data in Col A is edited
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, "hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

To use this code, right-click on the sheet tab and "View Code". Copy/paste
the set of code you want to the module.


Gord Dibben Excel MVP