Using VBA to save Track Changes History

[smithchuck]

I've been working on a shared file where I occasionally need to turn off sharing, but I don't want to lose the history log (produced by "Track Changes." I thought I had a "SaveHistoryLog" macro working fine, but then it seems to work sometimes and other times give different run-time errors 1004. I think I'm getting close to figuring out why it was working in one case and not the other, but maybe one of you can get me to my destination. Here's what I've done as I've been testing this tonight, starting from scratch:

Create new workbook.
Turn on sharing (with track changes, which saves file).
Make some changes to track & save file.
Record a "SaveHistoryLog" macro as I "Copy" the history sheet to a new workbook.
Unshare main workbook.
Copy recorded macro from external book (where it got recorded) into main workbook and edit a bit.
Turn sharing back on (saves file).
Make changes & save file.
Use Alt-F8 to run macro.
It works exactly as expected.

Unshared the file and edited the macro to turn off application alerts before & after saving history file, and then close the activeworkbook (which should be the history file).
Re-shared workbook, made changes, ran macro (using Alt-F8).
Worked exactly as expected.

Just to be sure:
Turned off sharing.
Turned on again, made changes, saved.
Worked perfectly (using Alt-F8 again).

Turned off sharing.
Added a button from Control Toolbox and set it to run the macro.
Turned on sharing, saved.
Made changes, saved.
Ran macro from button, got "Run-time error '1004': Method 'ListChangesOnNewSheet' of object '_Workbook' failed
(I've also seen "Method 'HighlightChangesOptions' of object '_Workbook' failed). The errors appear right before running the first "With ActiveWorkbook" line (see below).

Then immediately ran the same macro using Alt-F8, it worked fine.

Here's the code:

Sub SaveHistoryLog()
    With ActiveWorkbook
            .HighlightChangesOptions When:=xlAllChanges
            .ListChangesOnNewSheet = True
            .HighlightChangesOnScreen = False
    End With
    Sheets("History").Select
    Sheets("History").Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="Sharing testing 2 history.xls"
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

So it's starting to appear that running the code from a button causes it to fail, but running it using Alt-F8 is working (at the moment, but I haven't tested every possible situation yet).

Any ideas?

Thanks,
...Chuck

 

[smithchuck]

Anybody out there have any ideas on this? I'm still hoping to find a solution.

...Chuck