current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to write a VBA macro to track changes to a workbook in a separate sheet.

If you do this manually, the sequence of commands is Tools > Track Changes > Highlight Changes, taking the option Separate Worksheet. You have to do two iterations of the command, one to activate tracking inline, a second to move the tracking to a separate sheet.

Using the macro recorder, I got to this piece of code:

With ActiveWorkbook
    .Save
    .KeepChangeHistory = True
    .HighlightChangesOptions When:=xlAllChanges
    .ListChangesOnNewSheet = True
    .HighlightChangesOnScreen = False
    .Worksheets("History").Select
End With

When I run this, I get the error HighlightChangesOptions method fails. Any suggestions?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The HighlightChangesOptions method will only work if the workbook is already shared. In the UI, turning on HighlightChange will share the workbook automatically, but not so in VBA.

Application.DisplayAlerts = False
With ActiveWorkbook
    .SaveAs , , , , , , xlShared
    .KeepChangeHistory = True
    .HighlightChangesOptions When:=xlAllChanges
    .ListChangesOnNewSheet = True
    .HighlightChangesOnScreen = False
    .Worksheets("History").Select
End With

The DisplayAlerts call will prevent Excel from warning you that you are overwriting an existing workbook - itself. You may want to comment that line out just to see what's going on.

Note that this code cannot live in the shared workbook. Once you share the workbook, the code stops executing and errors. It has to live in a different workbook or add-in.

share|improve this answer
    
Thanks very much for your help. This effectively solved the problem, but like you say, the macro then has to be called from a different workbook. –  draconis Apr 10 '09 at 20:09
    
I still can not get this code to work. I put it in an add in but it is not tracking the changes. the error message went away once put in an ad-in though –  David Van der Vieren Jun 6 '13 at 16:47
    
What do you mean it's not tracking the changes? –  Dick Kusleika Jun 6 '13 at 18:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.