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

A

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.
 
G

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
 
L

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.
 
L

Ledigimate

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top