Resolved >>> How To: List Recent Changes to Document?

**joshjoneswas** · 10-22-2007, 09:59 AM

Hi Everyone,

I am in need of something that I cannot find anywhere on the net nor could I hack around and figure out on my own. Is there a way to list the recent changes to an excel document on the document itself?

For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:

B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm
A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm

I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.

Anyone have any ideas on this one?

Thanks!

**Myles** · 10-24-2007, 01:02 AM

Hi jos,

I have two ways to go about it.

Take #1
Make a copy of your entries (as you type) into another range - discreetly set apart. and set the scrollarea to “hide” . (Better still, you can copy to a hidden worksheet).

The following Worksheet Change event will keep track as you desire (with some bells and whistles!).

Please Login or Register  to view this content.

Rem: place in WorkBook module:

Please Login or Register  to view this content.

Take#2

Alternately, you could copy your entries (again as you type) into “Cell Comments”. Then deploy the following WorkSheet Change event to keep tabs of the record history

Please Login or Register  to view this content.

I would personally prefer Take #1 to Take #2, if because it is tidier.

See Attachments. Amend ranges to suit.

**joshjoneswas** · 10-24-2007, 10:21 AM

Hey that #1 option was great.

I amended it to keep a running change down a hidden column on another worksheet rather than changing each rows change cell to the right of it.

Now it will track changes indefinetly all the way down.

Thank you a TON for your help. I'm a VB guy but new to VBA and this was a HUGE help!

**Myles** · 10-25-2007, 02:05 AM

Jos,

My pleasure it works for you. I lost sight of the running change bit.

As you might have noticed, when an entry is text and not numeric, the declaration of V as Long (as I have it) will pose problems. Amend this declaration to: Dim V as Variant