Adding an updated/revised date/time on the end of row.

**HMIExcel08** · 03-11-2008, 04:52 PM

Hi All,

I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?

Thanks a lot.

**Richard Buttrey** · 03-11-2008, 06:09 PM

Originally Posted by HMIExcel08

Hi All,

I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?

Thanks a lot.

The following VBA code in the Sheet Selection_Change event will do it:

Please Login or Register  to view this content.

Adjust as necessary, the pre-set cell is L4, and the row is 4:4

**HMIExcel08** · 03-11-2008, 07:06 PM

Thanks a lot for your code!

**HMIExcel08** · 03-11-2008, 07:24 PM

Hi Richard,

I tested the code for recording date and time, it's amazing, except the date and time change even when the mouse only touch an empty cell and input/revising nothing on the selected row. Is there anyway to edit the code so that the date and time record only when press "Save"?

Thanks!

**Richard Buttrey** · 03-11-2008, 07:37 PM

Originally Posted by HMIExcel08

Hi Richard,

I tested the code for recording date and time, it's amazing, except the date and time change even when the mouse only touch an empty cell and input/revising nothing on the selected row. Is there anyway to edit the code so that the date and time record only when press "Save"?

Thanks!

Hi,

OK, I misunderstood your original requirement.

Put this code in the Worksheet_Change event, and not the Worksheets_Selection_Change event

This will change the cell whenever a cell on row 4 is changed. Clicking a cell won't now affect it

When you say press Save, do you literally mean when you save the file, and in that event, what do you want to do since time could have elapsed between changing a cell and saving the file. If this is the requirement then it may be possible to have the macro record the time, each time a cell is changed, and use the last recorded time to update the cell just before the workbook is saved.

Rgds

**HMIExcel08** · 03-11-2008, 08:00 PM

Hi,

I tested the code again and it works fantastically as what I wanted. I think this is enough. I appreciate it.

Have a great day!

**saganico** · 04-03-2008, 07:58 AM

Hi Gent's,

This is just what I was looking for with one excemption, I need to edit the ranges so that the date/time appears on the row the change has been made. The sheet consists of about 230 rows with port info for each port on each row. We want to have a "Last revised" column(T) that shows the date/time any changes was made to this port.

I'm not too familiar with this programming, so your help is highly appreciated

**Richard Buttrey** · 04-03-2008, 03:29 PM

Originally Posted by saganico

Hi Gent's,

This is just what I was looking for with one excemption, I need to edit the ranges so that the date/time appears on the row the change has been made. The sheet consists of about 230 rows with port info for each port on each row. We want to have a "Last revised" column(T) that shows the date/time any changes was made to this port.

I'm not too familiar with this programming, so your help is highly appreciated

Hi,

I'm not familiar with you particular workbook. Can you zip it up and attach here so that we can take a look.

Rgds

**saganico** · 04-04-2008, 03:10 AM

Hi!

Attached is a portion of the workbook. The transfer button is for transferring the information for one port to a separate sheet. In order to make it work one marks the row one want's transferred and click the button.

Column "T" is the one we would like the revised date/time to show for each port.

**Richard Buttrey** · 04-04-2008, 06:03 AM

Originally Posted by saganico

Hi!

Attached is a portion of the workbook. The transfer button is for transferring the information for one port to a separate sheet. In order to make it work one marks the row one want's transferred and click the button.

Column "T" is the one we would like the revised date/time to show for each port.

Hi,

Just responded via a PM.

I see that the code is already in the Worksheet_Change event for the Data sheet. At the moment any change in a cell in the range A2:S300 will alter the date and time in every cell in the range T2:T300. i.e. all those cells will contain the same Date/Time.

Since you just want the T cell, for the row in which the change has been made, to be changed, just substitute the code:

Please Login or Register  to view this content.

for the present code:

Please Login or Register  to view this content.

Rgds

**saganico** · 04-04-2008, 08:02 AM

Hi Richard,

Thanks a lot, now we're almost there. It works fine as long as I only move within that row, but if I press "Enter" after the input it updates the cell for the line below, if clicking on a different row after the input it updates the cell corresponding to the row I click, not the one the changes were made.
As a bonus, would it be possible to have date time and User inserted?

By the way, I can not find the PM.

**Richard Buttrey** · 04-04-2008, 08:30 AM

Originally Posted by saganico

Hi Richard,

Thanks a lot, now we're almost there. It works fine as long as I only move within that row, but if I press "Enter" after the input it updates the cell for the line below, if clicking on a different row after the input it updates the cell corresponding to the row I click, not the one the changes were made.
As a bonus, would it be possible to have date time and User inserted?

By the way, I can not find the PM.

Hi,

I suspect that may be because your application is set to move the cursor after an entry, (or perhaps left/right/up), check in your Tools Options Edit menu.

In which case either add the following first line to the procedure

Please Login or Register  to view this content.

And then in the Data Worksheet_Deactivate event add:

Please Login or Register  to view this content.

OR

Leave it as it is, and if the system is set to move the cursor down change the code to:

Please Login or Register  to view this content.

This updates the row above (the -1) if your system is set to move down. You'll need to change this if the system is set to move Up/Left/or Right

I've also added the Application.UserName bit so that it updates the users name.

HTH

**saganico** · 04-04-2008, 10:04 AM

Thanks again and sorry for bugging you with this...

The Username part was just what I was looking for.

Your suggested fixes appears to work, but I still have one problem. This workbook will be used by at least 50-60 different users world wide and I'm sorry to say there is only a limited knowledge among some of them. Hence I am trying to make this fool proof. You know there are almost as many ways to move in a worksheet as there are users. Some mouseclick the next cell they are going to edit and some use the arrowbuttons or tab butttons etc. Is there any other way to connect the changed row to the corresponding cell in the "T" coulmn other than ActiveCell? Something like "ChangedCell" or "EditedCell"... I wish...

Any Ideas?

**Richard Buttrey** · 04-04-2008, 10:17 AM

Originally Posted by saganico

Thanks again and sorry for bugging you with this...

The Username part was just what I was looking for.

Your suggested fixes appears to work, but I still have one problem. This workbook will be used by at least 50-60 different users world wide and I'm sorry to say there is only a limited knowledge among some of them. Hence I am trying to make this fool proof. You know there are almost as many ways to move in a worksheet as there are users. Some mouseclick the next cell they are going to edit and some use the arrowbuttons or tab butttons etc. Is there any other way to connect the changed row to the corresponding cell in the "T" coulmn other than ActiveCell? Something like "ChangedCell" or "EditedCell"... I wish...

Any Ideas?

Hi,

Yes. just substitute the code containing the "ActiveCell.Row" text in between the IF...EndIF lines with:

Please Login or Register  to view this content.

Rgds

**saganico** · 04-04-2008, 04:22 PM

Now it works great

Thanks very much!

**Damadark** · 07-09-2010, 06:19 PM

I have a time/date formula need. I need to be able to enter a date and time and have a hours and minutes interval calculate the original date time. tried to figure out the formula and research it online but never quite right.

Date/time - hours/minutes = Start Date/time
A1 is date and A2 is time, hours and minutes D3 and I wish for E3 to show the start date and time.

E.G. Jan 6 9:00am - 36H:12min = Jan 4 8:48am

any suggestions?

**robinmathew** · 03-14-2011, 12:03 PM

I know this is an old post but it was one that came closest to helping me find a solution similar to what the others members wanted assistance with.

I've attached a spreadsheet. You'll notice columns A-C. I'm trying to have each row have a last update time and date only if that particular row is changed. Each row is independent so each will have various time and dates.

How can I do this? I also am not very knowledgeable in VBA so I may need to be instructed as a beginner.

Thank you so much for any help you can provide.

**[email protected]** · 06-12-2012, 03:21 PM

I am having the same problem. Could someone supply me with a code to do the following:

I have many rows which I would like a respective cell to record the date a cell within the row has changed. As of now, the original code

Dim stMyDateTime As String
stMyDateTime = Date & " : " & Time
If Not Intersect(Target, Range("A2:S300")) Is Nothing Then
Range("T2:T300") = stMyDateTime
End If

is working. EXCEPT whenever I make a change, there is a deadly error, sending my worksheet into a crash.

I would like to be able to change a cell in the row and have a respective cell mark the date and time of the change. I would like the code to be applicable to all rows in a range and their respective cells. I would also like to not have to use macros when others are operating the spreadsheet. I've attached my workbook for help.

**arlu1201** · 06-13-2012, 03:25 AM

Nick,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.