Tracking Changes in XL?

[Zenkai]

Hi there,

I am writing a VBA to record all changes made to a document, and write them to Word. I already have the Word implementation, what I need to know is this:

How can I record all changes made to a document since it was opened? My only thought was to have the VBA record Macro data and store it as a String. If you know how to do that, or have any better ideas, please let me know.

Is there some kind of Track Changes function in XL? Thanks!

-Nick

 

[RobBroekhuis]

Give us some more detail about what you're trying to do.

"How can I record all changes made to a document since it was opened?"

Do you mean "to a workbook"?
Rob

 

[Zenkai]

Rob:

Yes, I did mean workbook (oops!)

Basically, I'd like every change that is made to be recorded in String format. For example, if the user hilights A1 and types "hello," I'd like that change recorded in some String. I want to be able to record everything, including undos, cel property changes, etc.

Those Strings will then be thrown into a Word document, which I can already do. Does that make more sense?

 

[RobBroekhuis]

It makes sense, but is not straightforward. Any changes to the CONTENTS of a cell can easily be captured using the workbook_sheetchange event handler. Unfortunately, I don't think there is an equivalent trigger for property changes, and even capturing contents changes is not foolproof, since it doesn't deal well with cut-and-paste operations etc. And no, I don't believe Excel has a built-in functionality like Word to track changes. You can very laboriously try to do it yourself by using the workbook_sheetselectionchange event to store all attributes of cells visited and compare them when exiting the cell, but even that fails to work when more than one cell is changed at a time.
This sounds like a problem somebody must have wrestled with before - maybe there is a solution out there...
Rob

 

[RobBroekhuis]

Oops. I'm wrong, of course... Excel has a perfectly good revision tracking tool, I've just never used it. I'll take a look at it now...
Rob

 

[RobBroekhuis]

Hmmm... The tracking tool only tracks content changes. It does do a good job of tracking cut/copy/paste operations, but I can't find a way to get to the changehistory information through VBA. Anybody?
Rob

 

[Zenkai]

Rob

Thanks very much. What's the code for the tracking tool you're familiar with? Even if it doesn't all work, it'd be nice to have a starting point.

I was trying the ListChangesOnNewSheet property, but I keep getting a "failed" error when I compile.

-Nick

 

[RobBroekhuis]

There's really no code. The only interface with VBA code I've found is through the KeepChangehistory and ChangehistoryDuration properties (see VBA help), but I can't find a way to access the actual change history info.
Rob

 

[Zenkai]

Well thanks very much for checking it out. Yeah, nothing in the Workbook class has anything dealing with the actual history itself . . . very sad.

-Nick

 

[Zenkai]

Hm . . .

How can I return a string to a process from a Worksheet_SheetChange? What would the code look like?

-Nick

 

[RobBroekhuis]

Describe the string you'd like to generate. Depending on that, the code would be more or less complex.
Rob

 

[Zenkai]

I guess I am unfamiliar with the way that event operates. I'd like to know the Cell's column, row, and formula. I'd also like to know the formula as it was prior to the change.

 

[RobBroekhuis]

This is the header for the event (generated automatically when you select the event from the dropdown on the workbook's code page)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

you can use the Sh and Target objects to get information about the changed cell (after the change):

MyRow=target.row
MyCol=target.column
MySheet=Sh.name
MyNewFormula=target.formula

However, you can't get the old information this way. To do that, you'd need to use the Workbook_sheetselectionchange event to record the old contents in a public variable whenever the user enters a new cell:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MyOldFormula=target.formula
End Sub

That should get you started, anyway.
Rob

 

[Zenkai]

Rob, thanks so much!

It's not perfect yet, but at least I have something started that I can work with . . . if only they'd have made the History accessible through VBA . . .

-Nick

 

[RobBroekhuis]

Good luck getting it right. If you succeed, please let us know - maybe even write a FAQ.
Cheers
Rob