![]() |
can excel automatically enter the date data in a cell was entered
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 |
can excel automatically enter the date data in a cell was entered
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 |
can excel automatically enter the date data in a cell was ente
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 |
can excel automatically enter the date data in a cell was ente
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 |
can excel automatically enter the date data in a cell was ente
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 |
All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com