Automatically show new date in the modified cell

  • Thread starter Thread starter NYBoy
  • Start date Start date
N

NYBoy

I like to add today's date next to the cell value.
Everytime the cell is modified, I want the date to be updated
automatically to today's date.

Any help is appreciated.

NYBoy
 
NYBoy, here is one way, this is worksheet code, right click on the worksheet
tab and view code, paste in this code

Private Sub Worksheet_Change(ByVal Target As Range)
'will put the date in column B when data is put in A2:A20
'change range as needed
On Error GoTo E
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:A20")) Is Nothing Then
With Target
.Offset(0, 1).Value = Format(Date, "mm/dd/yyyy")
End With
End If

E:
Application.EnableEvents = True
End Sub

You may also want to have a look here for more ways to do it
http://www.mcgimpsey.com/excel/timestamp.html


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Thanks Guys,

How about Timestamping in the same cell in the lower right corner where
data is entered.

Am I asking too much?

Thanks,
NYBoy
 
Did you look at the web page that was cited? What do you want to do
differently?
 
Yes, I looked at it. Thanks.
I'm not familiar with codes. I just copied and pasted and it works.

I want to take one step further - I want to know if time stamp date can
automatically appear in the same cell where the data is entered.

e.g. If I type the word "Released" in cell C2 and hit enter.

Then the cell C2 appear like this

RELEASED
10/04/05
 
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C2"), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = .Text & vbNewLine & _
Format(Date, "mm/dd/yyyy")
Application.EnableEvents = True
End If
End If
End With
End Sub
 
Thanks JE,
Getting close to what I need.
Little problem: When I edit the text, the old date stays there as well
and new date is also shown.
I just want the latest date.

Here's What I'm trying to do.
Let's say a job
Started on 10/2/05
Reviewed on 10/3/05
Released 10/4/05

So when I edit the "Reviewed" to "Released", I only want to se
"Released 10/04/05".
Also, a small rectagular box appers after my text (whatever it i
called). Can we get rid of it?

NYBoy.

I have to say again, however you guys generate these code languages,
can't understand but it works like magic.
Do you write these languages as people ask or they're already writte
by Microsoft
 
Back
Top