CODEWITHSUNDEEP
excelms-wordvba
Martin Dimitrov
Martin Dimitrov

Reputation: 4956

Is there a way to trigger "track changes" through VBA in Excel?

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.

Upvotes: 2

Views: 9997

Answers (3)

eriklind
eriklind

Reputation: 31

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.

Upvotes: 0

user1143192
user1143192

Reputation: 9

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

Upvotes: 1

brettdj
brettdj

Reputation: 55682

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.

Upvotes: 1

Related Questions