Please help with auto Date and Time with VBA

[Fonz]

I have been all over the place trying to do this and I am completely lost.
Please help. I used the =Now function in excel to display the date and time
in a cell when another cell had information entered into it. This proved to
be nice until I realized that the adte and time was updating ANYTIME I made a
change to anywhere on the sheet. I need the date and time to remain static
after it appears in the active cell the first time.

After snooping around the net, it seems that the answer is in using a VBA
code which I have no inkling oon how to use, and the codes that were posted,
I wouldn't know how to change to meet my specific excel sheet. Also in the
instructions on the webpage said to right click on the sheet page and choose
to view code, which I did got a big window that I couldn't do ANYTHING in.
There was no place to put the code, and there are 3 items on the left, ther
are:

atpvbaen.xls
funcres.xls
VBAProject

When I try to open any of those, there is a password, exceot the VBAProject
which is unviewable.

What I need is simple if anyone would be kind enough to help me out. I need
I am guessing 2 codes.

I need the code to display the date and time in column E if data was entered
in the same row in column D. I also need the code to dispolay a date and time
in column G if information has been entered in the same row in Column F. The
date is dependent on information from only the same row and shouldn't be
affected by information entered on another row.

Please explain to me also what I am doing wrong that I am unable to enter
the code into excel when right clicking on the sheet and choosing to view
code. This would be much appreciated. Below is the code I found on the net
that I have no idea of the functionality of it. Thanks in advance for all who
help.





Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("B:B")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Add the Date in Column C whenever an entry is made in the same row in
Column B
If Target <> "" Then Target.Offset(, +1) = Date
' Clear the cell in Column C if the same row in Column B is cleared
If Target = "" Then Target.Offset(, +1) = ""
End Sub

 

[Jim Thomlinson]

You need to place this code into a standard code module (not directly into
the sheet). To create a standard code module just record a macro (anything
will do). Right click on any tab and select view code. Now in the project
explorer double click on the Module. You should now see the VBA for the macro
you just recorded. You can delete all of the code in here if you wish. Now
paste this code...

Public Function StaticDate() As Date
StaticDate = Now()
End Function

In your spreadhseet you can now enter the formula

=StaticDate()

Into any cell and the date will appear ( you may need to change the
formatting). This date will not change...

 

[Fonz]

Will that also add the time or just the date? I need both time and date to
appear in the cell. Also, How do you record a macro?

 

[Jim Thomlinson]

Yes that is time and date. How about this lets just skip the recording of a
macro. Right click the sheet tab and select view code. On the left should be
the project explorer. In the explorer you will see atpvbaen.xls, ... and your
current spreadsheet. You will see something like Sheet1(The Sheet name),
Sheet2(The Sheet name). From the Menu click Insert -> Module. A module will
be added to your workbook (Called Module1). Double click on this module and
paste the code...

 

[Fonz]

I did as you said, from your first post as there wasn't sheet 1 or 2 in the
left pane, added the code after deleting what the macro posted, now I have
this in a cell.

=IF(D2=0,"",StaticDate())

The cell is indeed blank if d2=0, but when I enter a value into D2, I get
#NAME? error. What went wrong?

Thanks

 

[Jim Thomlinson]

In excel palce the cursor in a blank cell. Now select Insert -> Function. IN
the Category Drop down select User Defined. Scroll through the options. If
you have successfully enetered the code I gave you into a standard module
then StaticDate will be listed as an option. Let me know how that goes...

 

[Fonz]

Hey man, just an update, I did as you said, found that the code was a bit
different but entering the code you initially gave me did work, I just as you
said, had to update the formatting and it worked like a charm. I am gonna
keep our conversation safely placed for future reference. Thanks for your
help.