How do I timestamp a cell in excel (I don't know VBA)

[avalonchris]

I need to add a timestamp (mm/dd/yyyy hh:mm:ss AM/PM) in column B1 when a
user enters a value in column A1. The same is true for B2 when a user enters
A2. If a user enters A2 then it should not change B1.

Thoughts? I have found solutions but unable to get them to work (newbie in
excel VBA). I am also using EXCEL 2007.

 

[Dave Peterson]

Take a look at JE McGimpsey's site:
http://www.mcgimpsey.com/excel/timestamp.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Ledigimate]

When you say "column" A1, do you actually mean cell A1, are you referring to
the entire column A? This is important to know in order to suggest the
correct VBA code sample.

 

[Ledigimate]

After a re-read of your question, I understood what you meant. The code in
Gord Dibben's reply does the trick.