删除工作表/图表事件VBA [英] Delete Sheet/chart event VBA

问题描述

我正在尝试删除表格(Excel)时触发宏. 自2010年以来，发生了不同的删除事件:

I am trying to trigger a macro when a sheet (Excel) is deleted. Since 2010 there are different delete events:

应用程序级别:Application.SheetBeforeDelete事件.

Application Level: The Application.SheetBeforeDelete event.

工作簿级别:Workbook.SheetBeforeDelete事件.

Workbook Level: The Workbook.SheetBeforeDelete event.

工作表级别:Worksheet.BeforeDelete事件.

Worksheet Level: The Worksheet.BeforeDelete event.

作为初学者，我无法应用最后两个井，但是我尝试应用第一个井.

As a beginner I was not able to apply the last two well, however I tried to apply the first one.

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object) 
Call Macro
End Sub

这在工作表上工作正常，但在图表(仅包含图表的工作表)上不起作用.

This works fine on sheets, but it doesn't work on charts (sheets that only contain a chart).

我的目标是调整计数器并可能重命名工作表，因为我的工作表名为Sheetname(1)...(2)..并且用户经常删除工作表.

My goal is to adjust my counter and possibly rename the sheets, as my sheets are named Sheetname(1),..(2).. and the user frequently deletes sheets.

我进行了很多搜索，但找不到任何可以遵循的示例.

I searched a lot but I couldn't find any examples that I could follow.

任何帮助将不胜感激.

推荐答案

这似乎是对实现的监督，应该根据设计实现此事件.实际上，Sheets集合是Worksheets和Charts集合的并集.由于事件的名称为Workbook_SheetBeforeDelete(而不是Workbook_WorksheetBeforeDelete)，因此常识表明该事件应适用于所有Sheets，即Worksheets和Charts.

It's looks like an implementation oversight, this event should have been implemented according to the design. In fact, the Sheets collection is the union of the Worksheets and Charts collections. Since the name of the event is Workbook_SheetBeforeDelete (not Workbook_WorksheetBeforeDelete), common sense dictates that the event should apply to all Sheets, that is, both Worksheets and Charts.

请注意，图表和工作表都会引发其他事件，例如Workbook_SheetDeactivate.这可以确认错误，但也可以通过利用Workbook_SheetDeactivate事件来建议解决方法.

Notice that, other events such as Workbook_SheetDeactivate are raised for both Charts and Worksheets. This confirms the error but also suggests a workaround by exploiting the Workbook_SheetDeactivate event.

我们可以在ThisWorkbook代码模块中添加两个过程. checkChartDelete()检查图表是否已删除并启动适当的操作.它需要通过Application.OnTime进行调用，因此它是通过静态变量chartNameToCheck来获取其参数的.

We can add two procedures to ThisWorkbook code module. checkChartDelete() checks if a chart has been deleted and launches the appropriate action. It needs to be called through Application.OnTime, so it gets its parameter through a static variable chartNameToCheck.

' Code Module ThisWorkbook
Option Explicit
Private chartNameToCheck As String
Private Sub checkChartDelete()
    On Error Resume Next
    Dim x: Set x = Sheets(chartNameToCheck)
    If Err.Number <> 0 Then
        '**********************************************
        ' call or do here the action on chart deleted '
        '**********************************************
        MsgBox "chart deleted: " & chartNameToCheck
    End If
    chartNameToCheck = ""
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If TypeName(Sh) <> "Chart" Then Exit Sub
    chartNameToCheck = Sh.name
    Application.OnTime Now, "ThisWorkbook.checkChartDelete"
End Sub

这篇关于删除工作表/图表事件VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！