Excel VBA宏可在单独的工作表中跟踪更改 [英] Excel VBA macro to track changes in separate sheet

问题描述

我正在尝试编写VBA宏，以在单独的工作表中跟踪对工作簿的更改.

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

运行此命令时，出现错误 HighlightChangesOptions方法失败.有什么建议吗?

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

推荐答案

HighlightChangesOptions方法仅在工作簿已共享时才有效.在用户界面中，打开HighlightChange将自动共享工作簿，但在VBA中则不会.

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

DisplayAlerts调用将阻止Excel警告您正在覆盖现有工作簿本身.您可能只想注释一下该行，以了解发生了什么情况.

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.

这篇关于Excel VBA宏可在单独的工作表中跟踪更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！