When did that happen?? – Tracking Changes

  • Excel has a built-in capability to track changes made to a worksheet, (TOOLS > TRACK CHANGES) and it has several options for how to implement it – including selecting a set from the previous value, the new value, who made the change, and a time stamp of when it was made. It enters this change documentation in the changed cell’s comment (when you accept the change).

    However, if this is not the first change made to the cell, the new comment overwrites any previous comment in the cell. If you want to retain previous comments, the following code, placed into the sheet’s private module will insert the new time-stamped information ahead of the existing comment. The comments are thus stored in reverse chronological order.

    If you are not on a shared system where each user has a different username, then remove the environ(“username”) portion of the code as it will provide no useful information and just make the comments longer.

    EDIT: I forgot to mention in the initial posting that the code below is set to insert comments only in column E (5) below a header in row 1. Remove/revise these conditions to comment to all/other cells. Also, the Target(n) syntax allows the code to handle simultaneous user input to a range with Ctrl+Enter. Only the first cell in the range gets the previous value included in the comment (I'm not sure how to trap the previous value for all of the range cells; I believe that Public variables cannot be arrays.)

    SIDEBAR: If you want to be stealthy about the information you are capturing, consider such things as turning off the comment indicator display as you exit the macro and also using sheet protection to prevent a user from editing the comments (protect objects, but you’ll probably want to unlock all of the cells so the sheet can otherwise be used. If you use sheet protection, you’ll need to turn if off before writing the documenting comment and turn it back on as you exit the change detection macro.
    EDIT2: If you want to more stealthly, write the data to a VeryHiddenWorksheet. See this thread for one way to write to a sheet.

    Enjoy! Hope this is useful to someone.