用于跟踪单独工作表中的更改的 Excel VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/721160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA macro to track changes in separate sheet
提问by draconis
I am trying to write a VBA macro to track changes to a workbook in a separate sheet.
我正在尝试编写一个 VBA 宏来跟踪对单独工作表中工作簿的更改。
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?
当我运行它时，我收到错误HighlightChangesOptions method failed。有什么建议？
采纳答案by Dick Kusleika
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.
HighlightChangesOptions 方法仅在工作簿已共享时才有效。在 UI 中，打开 HighlightChange 将自动共享工作簿，但在 VBA 中则不然。
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs , , , , , , xlShared
.KeepChangeHistory = True
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
.Worksheets("History").Select
End WithThe 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.
DisplayAlerts 调用将阻止 Excel 警告您正在覆盖现有工作簿 - 本身。您可能想对该行进行注释，只是为了看看发生了什么。
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.
请注意，此代码不能存在于共享工作簿中。共享工作簿后，代码将停止执行并出错。它必须位于不同的工作簿或加载项中。
