如何捕获通过 Excel VBA 中的复制/粘贴添加的工作表

Question

我正在尝试捕获从另一个工作簿复制到工作簿中的工作表。
从另一个工作簿复制工作表时，不会触发 Workbook_NewSheet 事件。 仅当用户通过（插入 -> 工作表菜单选项）手动插入它们，或者通过 VBA 添加新工作表作为 ThisWorkbook.Worksheets.Add 时，才会触发它。

我试图捕获的基本上是一个粘贴操作，它会产生一个新的工作表。

这可能来自以下任何用户操作：

1. 用户通过按住 Control 键拖动现有工作表来复制现有工作表（这会添加新工作表）
2. 用户从另一个工作簿复制工作表
3. 用户从另一个工作簿

或以下任何 VBA 代码 移动工作表：

SourceWorkbook.Sheets(“SourceSheet”).Copy Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'copy across workbook'  
SourceWorkbook.Sheets(“SourceSheet”).Move Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'move across workbook'  
ThisWorkbook. Sheets(“SheetName”).Copy 'copy within workbook'  

如果您知道在 VBA 中捕获此操作/宏结果的任何方法，那将会非常有帮助。

请注意，我不想避免这样的用户操作（所以我不想保护工作簿），但我想以编程方式处理粘贴的工作表以验证数据，如果类似的工作表已存在，则更新现有工作表而不是在两张表中具有相同的数据。

Answer 1

复制工作表时，其名称始终以“(2)”或至少“)”结尾。 你可以这样检查

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like "*(2)" Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
    End If
End Sub

Answer 2

SheetActivate 事件在所有这些情况下都会触发。 显然它也会在许多其他情况下发生。 这听起来像是一个巨大的痛苦，但您可以维护自己的工作表集合，并将您的集合与 ThisWorkbook.Sheets 集合进行比较，以查看是否添加/删除了某些内容。

如果您试图阻止它，您可能会考虑保护工作簿结构而不是在代码中执行此操作。

Answer 3

我实现它的方式是

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToggleMenuOptions False, 848, 889
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
ToggleMenuOptions True, 847, 848, 889
End Sub

Public Function ToggleMenuOptions(bToggle As Boolean, ParamArray ControlID() As Variant) As Boolean
'848 Move or Copy Sheet...
'889 Rename Sheet
'847 Delete Sheet
On Error GoTo lblError
Dim oControl As CommandBarControl, oControls As CommandBarControls, iControl As Integer
If IsMissing(ControlID) Then
    ToggleMenuOptions = False
    Exit Function
End If

For iControl = LBound(ControlID) To UBound(ControlID)
    For Each oControl In Application.CommandBars.FindControls(ID:=ControlID(iControl))
        oControl.Enabled = bToggle
    Next
Next
ToggleMenuOptions = True
Exit Function
lblError:
    If Err.Number Then
        ToggleMenuOptions = False
        Exit Function
    End If
End Function

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "Please use Add New Project option in custom Toolbar to add new sheets!!", vbExclamation, "Not Supported"
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End Sub

这样我的用户将无法重命名、添加或删除工作表。 目前这工作得很好。

Answer 4

我能想到的在不维护单独的工作表集合的情况下执行此操作的唯一方法是维护工作表名称（或工作表代号）的静态数组，并在每次 SheetActivate 事件触发时将其与工作簿中的实际工作表进行比较以检测任何添加。 如果您不想/不能将列表保留在数组中，您始终可以使用隐藏表来存储列表。 这是否比维护单独的集合更痛苦是值得商榷的:)

Answer 5

我正在研究类似的东西，但无法阻止任何用户菜单操作。 我的工作表的类型很重要 - 每张工作表要么是主工作表，要么是从工作表 - 每个主工作表都会对其下面的从工作表求和，我需要保持这些公式干净。

我不是在额外的隐藏工作表中维护工作表列表，而是在每个工作表上定义 2 个隐藏名称，记录工作表索引与其链接的主工作表的偏移量以及对链接的主工作表的引用。 因此，如果我的工作表距其主工作表 +2 个选项卡，则在工作表激活/停用（不确定现阶段跟踪哪个更好）时，如果插入、删除或移动任何内容，则此偏移量将发生变化。 这涵盖了移动或复制工作表时可能发生的大部分或全部事件。

如果工作表已移动，我会循环浏览工作簿并计算每张工作表的新主/从索引引用。

当我得到这个相当稳定的结果时，我会发布代码，但这似乎是一个可以在多种情况下工作的方案。