can excel automatically enter the date data in a cell was entered

[mg_sv_r]

Can I get excel to enter the date in one cell that data was entered in
another, without relying on the user to input this data?

I have tried the TODAY() function but this changes the date each time te
sheet is reloaded!

Thanks in advance for any help.

John

 

[Bernie Deitrick]

John,

Copy the code below, right-click on the sheet tab, select "View Code", and paste the code into the
window that appears.

It will put the date in column B (in the same row) for any change in column A, if the change is done
to a single cell and not to a group of cells.

Obviously, the code can be modified to apply to any range of entered cells....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target(1, 2).Value = Date
Application.EnableEvents = True
End Sub

 

[mg_sv_r]

Thank you so much Bernie. Works great.

John,

Copy the code below, right-click on the sheet tab, select "View Code", and paste the code into the
window that appears.

It will put the date in column B (in the same row) for any change in column A, if the change is done
to a single cell and not to a group of cells.

Obviously, the code can be modified to apply to any range of entered cells....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target(1, 2).Value = Date
Application.EnableEvents = True
End Sub

 

[Jim]

Bernie,

I'm looking to do this same function, but only for two specific cells. I'm
not advanced enough with VBA to determine what code to modify below. I wish
to autumatically put the current date/time in cell P2 when ever the cell in
N2 is modified. The same for P3 and N3.

Thank you,

JIM

 

[Roger Govier]

Hi Jim

there are several ways of limiting the cells to be activated, but modifying
Bernie's code to the following will achieve what you want

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 14 Then Exit Sub
If Target.Row <> 2 And Target.Row <> 3 Then Exit Sub
Application.EnableEvents = False
Target(1, 2).Value = Date
Application.EnableEvents = True
End Sub

Column N is column 14, so if that isn't the column that has just been
entered, then exit the code
If the row is not 2 and not 3, then also exit the code