Static Date and Time in Excel 2007

[MilleniumPro]

Hello all,

I have googled around and have not been able to find the answer on the
following:

I am looking to have a cell that automatically populates the current date
and time and leave that information STATIC in that cell once populated when
an adjacent cell has a value entered into it. The only answers I fould were
for excel 2000 and 2003. I am using 2007 and I am totally lost with the new
menu system that has been implemented here. If this requires a macro, please
help me walk through how to enter the area where the macros is to be enter
along with any supporting code. Thanks to anyone in advance that can help.

 

[Sheeloo]

right-click on the sheet tab and choose view code
paste the code below in the VB Editor which opens up
close it and enter anything in A1. the code below will enter the time in B1
everytime A1 changes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1") = Now()
End If
End Sub

 

[Niek Otten]

Look here:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Shane Devenshire]

Hi,

Well that question leads to a problem - suppose you enter data in A1 then
the date is entered in B1, but now B1 has something in it so C1 gets a date,
and so on and so on.

Now lets suppose you want to do this only for entries made in column A then

Right-click the sheet tab and paste in the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range
Set Isect = Application.Intersect(Target, [A:A])
If Not Isect Is Nothing Then
Target.Offset(0, 1) = Now
End If
End Sub

 

[MilleniumPro]

Hello Shane and all,
Thanks for all of your responses. This is my current set up. I am using an
existing sheet that already had code from when I created this sheet in excel
2003, keep in mind I am now using excel 2007. The following code allows for
any text entered in Column A to be automatically capitalized to allow for
quick entry and to ensure there are no errors here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Target is an arguments which refers to the range being changed
If Target.Column = 1 Or Target.Column = 2 Then
'this check is to prevent the procedure from repeatedly calling itself
If Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)


End If
End If

End Sub


Now I want to keep this code. In addition, I would like to specifically if a
value is entered in b2, I want the STATIC TIMESTAMP to automatically
complete in E2. Then, When I enter a value in F2, I want another current
time stamp to be entered in G2. This is to happen in every row as I enter
values in each. So lets say a student arrives at school. I want to type in a
value in the B2 that says they arrived and the date and time to appear in
E2. When they leave, I want to type in a value in F2 and the time and date
to appear in G2. So every row is indepent for each student for when they
arrive and when they leave.

Being that I have current existing code for automatically creating capital
letters, Do I need to enter that code, or place code for this different
function underneath the code I pasted above?

Also, having used the NOW() function in the past, in Excel 2007 it is
displaying the time in 24 hour format when I would prefer to have the date
and time in this format:

01/01/2009 1:00PM

Again, thanks to all who can help as I am not at all good with coding but
could really use the help.

Hi,

Well that question leads to a problem - suppose you enter data in A1 then
the date is entered in B1, but now B1 has something in it so C1 gets a
date,
and so on and so on.

Now lets suppose you want to do this only for entries made in column A
then

Right-click the sheet tab and paste in the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range
Set Isect = Application.Intersect(Target, [A:A])
If Not Isect Is Nothing Then
Target.Offset(0, 1) = Now
End If
End Sub
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Hello all,

I have googled around and have not been able to find the answer on the
following:

I am looking to have a cell that automatically populates the current date
and time and leave that information STATIC in that cell once populated
when
an adjacent cell has a value entered into it. The only answers I fould
were
for excel 2000 and 2003. I am using 2007 and I am totally lost with the
new
menu system that has been implemented here. If this requires a macro,
please
help me walk through how to enter the area where the macros is to be
enter
along with any supporting code. Thanks to anyone in advance that can
help.