2

I want from VBA to start/stop "Track changes" functionality in Excel.

Searching thru the Net I saw a property called TrackRevisions of ActiveDocument object. Supposedly in MS Word writing ActiveDocument.TrackRevisions = True should turn on "Track changes".

But in MS Excel this line gives 424 Object required run-time error. Same error is returned when trying with ThisWorkbook. Changing it to ActiveSheet brings 438 Object doesn't support this property or method error.

Community
  • 1
  • 1
Martin Dimitrov
  • 4,796
  • 5
  • 46
  • 62

3 Answers3

1

For a shared workbook you can use VBA from these links (the method is ActiveWorkbook.HighlightChangesOptions)

This doesn't offer the same depth of tracking as available in Word, for example from the first link in my post,in Excel:

  1. Change tracking differs from undo and backup
  2. Some types of changes are not tracked Changes that you make to cell contents are tracked, but other changes, such as formatting changes, are not tracked.
  3. Change history is kept only for a specific interval
  4. Change history is periodically deleted

If that isn't what you were chasing you may be able to employ specific VBA to track

  1. certain cells, or
  2. compare versions

But if that is the case we will need more information from you as to what you are chasing.

brettdj
  • 54,857
  • 16
  • 114
  • 177
  • `HighlightChangesOptions` seems to be working ONLY when change tracking is enabled. But how to turn change tracking on thru VBA? – Martin Dimitrov Jan 11 '12 at 14:55
1

You can use the following code. You will see this code when you record a macro. 

    With ActiveWorkbook
    .HighlightChangesOptions When:=xlAllChanges
    .ListChangesOnNewSheet = False
    .HighlightChangesOnScreen = True
   End With
user1143192
  • 9
  • 1
  • Thanks for the answer. I get 'Run-time error: 1004 Method HighlightChangesOptions of object _Workbook failed'. – Martin Dimitrov Jan 11 '12 at 13:46
  • Try the code here: http://windowssecrets.com/forums/showthread.php/138285-Tracking-changes-problem-in-Excel-2007?p=803592&viewfull=1#post803592 – natem345 Apr 08 '13 at 19:04
0

The questions is really what do you want to achieve. If you want to track changes in a spreadsheet, I assume you have some other users editing the workbook, and you want to record who changed what, as well as review/approve any modifications later on. Well, you don't actually need a triggering macros for that.

... instead of Track Changes, try comparing two workbooks using the Microsoft's Spreadsheet compare (application is limited to Excel 2013, Excel 2016 , Office 365 Professional).

... instead of Track Changes, you can record history of changes made to the workbook (who changed what and when) - XLTools Version Control.

I use both depending on the task.

eriklind
  • 31
  • 2