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

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" wrote in message
...
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

Thank you so much Bernie. Works great.

"Bernie Deitrick" wrote:

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" wrote in message
...
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,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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
--
Regards
Roger Govier

"Jim" wrote in message
...
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

"Bernie Deitrick" wrote:

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" wrote in message
...
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