Update Log

[bslynn987]

Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.

 

[Tinbendr]

I think the only solution to this is to make a backup on startup, then compare the two files after the update.

 

[Mohammad Basem]

Why not using the Track Changes feature?

 

[bslynn987]

Thank you for all your feedback. Rather than sending the information to a text format, I used a Workbook_SheetChange macro which records all updates on a very hidden sheet within my file.

FYI, The Track Changes feature is helpful if few changes will be made to the file, however, not beneficial for my purposes as I was wanting a journal of all updates made. The journal indicates what the value was of a cell prior to the update, what the value is after the update in addition to the time and date that the updates were made.

 

[Mohammad Basem]

The journal indicates what the value was of a cell prior to the update, what the value is after the update in addition to the time and date that the updates were made.

But the track changes saves all these information and more. Also it allows displaying the changes on the working sheet or on a separate sheet, the same way you did.
I have tested it and saved more than 1000 records of changes. I don’t think this could be considered as few changes.

 

[bslynn987]

The Track Changes feature can is effective only when updating 1 cell at a time. With this particular file, I am using event macros so multiple files could be updated at once. Also, the Track Changes feature doesn't remain in the change history log forever. In fact, once 30 days have passed since a change was made, Excel discards it from the log. I am looking for something that will provide me with ALL updates that a user made to a file and need the ability to review the information at any time (no expiration). The Workbook_SheetChange function that I am currently using is not working correctly either...are you familiar with any other options?

 

[Mohammad Basem]

You have mentioned more than once that track changes is effective with few changes or one cell. May be I did not understand your point properly, but I have used it to copy 3 sheets with more than 2,300 filled cells (Copy All and paste) and everything was registered. I think this is not a few changes or updating one cell at a time. It is rather mass changes.

On the other hand, the 30 days is the default value. You can increase it up to the maximum limit of a signed integer, which is 32767 days (more the 89 years…almost no expiration). However, management of the history will be required to keep the size of the workbook manageable. This would be similar to your approach of having separate log workbook.

Workbook_SheetChange and track changes keep track of editing changes and cannot track format changes. Please correct me if I am wrong, I think both have the same limitations and cannot go beyond editing changes.

Unfortunately, I am not familiar with other options, as the track changes was doing its job.

 

[bslynn987]

Thank you very much time for all your support on this issue. My main concern is that my file has even macros, therefore, upon updating any cell in column A, something will automatically populate in column E. Considering that I am utilizing the event macros, the track changes in excel has not been working properly for the file.

 

[Mohammad Basem]

You are right, if you want to have full access to your macros then track changes won't help.
Good luck.