The Automatic Timestamp Macro is Not Working Correctly on My File


I watched your video on how to enter a macro code that allows automatic data entry timestamps and an updated time stamp for when data is modified, and after following the directions it didnt immediately work for me as it did in the video.  However, after closing the document and reopening it, for some reason the time stamps started to show up.  The issue i'm having now is that all of the data on the "Summary Sheet" of the  Workbook where i've applied the macro is linked to data in other Sheets in the Workbook.  So there are no hard entries of data occuring on the Summary Sheet itself for rows E through N.  And when I update the data on another sheet, which subsequently updates that linked data on the Summary Sheet, the time stamp on the summary sheet does not update, eventhough data in a particular row has changed.  What is the fix for this?  I've uploaded a sample workbook with two sheets, a Summary Sheet and a Termite Treatment Data Sheet where the Summary Sheet will pull data from.  E15-N15 of the Summary Sheet are the ones that are linked to data in Column B of the the Termite Treatment Data Sheet.   Making selections or entering data in Cells B4:B8 of the Termite Treatment Data Sheet Data will generate the values that get linked into the Summary Sheet.  This Summary Sheet needs to be set up so that when someone makes any changes to entries in a data sheet that are linked to the Summary Sheet, the "Last Updated" timestamp cell will properly update, and preferably that timestamp cell will highlight yellow.  Getting that cell to highlight yellow if it changes is a conditional format that's probably a different topic, but ultimately that's what I need this Summary Sheet to do.  I also want to add an acknowledge button at the end of each row, that once pushed or selected, will unhighlight the Last Updated timestamp cell.  The idea here is that the person responsible for reviewing the summary sheet is alerted everytime a row has been updated.  That's why I want the "Last Updated" cell to highlight yellow when it changes.  That person also needs to acknowledge that most recent update, by clearing the highlight by some automatic means at the push of a button or check of a box.  This way if that row gets updated again, it will highlight yellow again, and can again be acknowledged to clear the highlight and that process can be repeatedly indefinitely.  Again, probably a separate topic, but if you can help me figure those out it would be a hugh help.  Priority however, is to get the "Last Updated" timestamp to actually update when linked data is changed.



sure, just edit your question and upload a sample file so someone can take a look. and also explain the issue here in the question in detail.
don (rep: 1979) Sep 15, '20 at 2:30 am
Add to Discussion


Selected Answer

Please review your logic. You say "Summary!E15:N15 is linked to 'Termite Date'!B:B". That assumption is at the bottom of your trouble. The truth is that 'Termite Date'!B:B is linked to Summary!E15:N15.

That means that whatever is entered on the Termite Data sheet is reflected in the summary and not the other way around. Therefore, when you make an entry in the Data sheet no Change Event occurs in the Summary sheet, and it's the Change Event that drives the time stamp.

Therefore, what you really want is that a time stamp is applied in the Summary when you make a change in the Data. To achieve this with the code you have you need to make two changes, one big and one small. Here is the small change. Replace the existing lines in your code with the ones below.

'Column for the date/time
Set myDateTimeRange = Worksheets("Summary").Range("O" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Worksheets("Summary").Range("P" & Target.Row)

Of course, the Worksheet_Event occurs on the Data sheet, not the Summary sheet. That's why waiting for it to happen on the Summary sheet requires infinite patience. Better make the big change already forecast. Move the procedure to the code sheet of the Terminate Data sheet and adjust myTableRange to show where the changes really occur.

This change is likely to give you a headache because of the difference in size between E15:N15 and B:B. I think this has to do with the implicit logic of your workbook which reflects the data in the summary, not the opposite direction. Therefore entries should be made in the data sheet, and that includes the time stamp. You can then import a copy of the time stamp from the data sheet using the same method by which you get the rest of the data.



So to clarify, I need to cut the entire code from the Summary Sheet and Paste it to each individual Data Sheet with or without the small change you mentioned included? Does the small change in the code still need to be made if I move the entire code to the individual data sheets where the change event occurs, assuming I link that updated date back to the Summary Sheet just like the rest of the data?
Karnold (rep: 2) Sep 15, '20 at 9:08 pm
In principle, yes. However, please don't try to follow my instructions blindly. You do have very good and much clearer instructions interspersed into the original code. Best way, just follow those instructions and set up the procedure for each sheet individually, specifying the trigger, the target and what to do (date or colour or date and colour) with the latter. Use details from my above answer only as a source for the syntax by which you address ranges in another sheet or set/remove fill colour. Once you understand what you're doing very little can go wrong. 
Variatus (rep: 4864) Sep 16, '20 at 9:49 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login