问题描述
我正在尝试编写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宏可在单独的工作表中跟踪更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!