Static Time/Date Stamp

[keith.e.graham]

How can I set up a Static Time/Date stamp to be posted in a cell
whenever another specific cell has any information in it or is this
possible? I never want the time or date to change.

Cell "A1" is empty until I enter info into it. Probably text, not a
number.
Cell "A2" is the date/time stamp cell. It will show a static date as
soon as info is
entered into cell "A1".

Thanks.

Keith

 

[Jake Marx]

Hi Keith,

How can I set up a Static Time/Date stamp to be posted in a cell
whenever another specific cell has any information in it or is this
possible? I never want the time or date to change.

Cell "A1" is empty until I enter info into it. Probably text, not a
number.
Cell "A2" is the date/time stamp cell. It will show a static date as
soon as info is entered into cell "A1".

You could use a UDF (user-defined function):

Public Function DATESTAMP(WatchedCell As Range) As Variant
If Len(WatchedCell.Value) Then
DATESTAMP = Now()
Else
DATESTAMP = ""
End If
End Function

In cell A2, you would enter:

=DATESTAMP(A1)

Although this is simple, it will change the datestamp any time the value in
the watched cell is changed. This may not be what you want. If you don't
want this, you'd probably want to use the Worksheet_Change event to watch
for the first change:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Len(Range("A2").Value) = 0 Then
Range("A2").Value = Now()
End If
End If
End Sub

This one goes "behind" the worksheet. To do this, you can right-click the
worksheet tab and select "View Code". Just paste the code above into the
resulting codepane, and it should work.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[keith.e.graham]

Jake,

Worked like a champ. Many thanks. I am not an expert at visual basic.
More like a beginner.

Keith

Hi Keith,

How can I set up a Static Time/Date stamp to be posted in a cell
whenever another specific cell has any information in it or is this
possible? I never want the time or date to change.

Cell "A1" is empty until I enter info into it. Probably text, not a
number.
Cell "A2" is the date/time stamp cell. It will show a static date as
soon as info is entered into cell "A1".

You could use a UDF (user-defined function):

Public Function DATESTAMP(WatchedCell As Range) As Variant
If Len(WatchedCell.Value) Then
DATESTAMP = Now()
Else
DATESTAMP = ""
End If
End Function

In cell A2, you would enter:

=DATESTAMP(A1)

Although this is simple, it will change the datestamp any time the value in
the watched cell is changed. This may not be what you want. If you don't
want this, you'd probably want to use the Worksheet_Change event to watch
for the first change:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Len(Range("A2").Value) = 0 Then
Range("A2").Value = Now()
End If
End If
End Sub

This one goes "behind" the worksheet. To do this, you can right-click the
worksheet tab and select "View Code". Just paste the code above into the
resulting codepane, and it should work.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]