如果用户插入新工作表，则自动运行宏。

Question

如果我的宏检测到用户将一个新工作表插入到工作簿(现有的和新的)中，我希望它能够自动运行。

 Sub macro_run()

    Dim Newws As Worksheet
    Dim wb As Excel.Workbook
    Dim sample1 As Worksheet

    With ThisWorkbook
        Set sample1 = .Sheets("Template")

        For Each wb In Application.Workbooks
            If Newws = sample1 Then
                Application.Run "PERSONAL.XLSB!Opennew"
            End If
        Next wb
    End With

End Sub

Answer 1

正如注释中提到的，您需要在WorkbookNewSheet级别处理Application。

'-- Create a new Class.
'-- Name it clsGlobalHandler.
'-- Following Code goes in that class

'/ Create a variable to hold Application Object
Public WithEvents xlApp As Application

'/ Handle NewSheet event. Invoked whenever a new sheet is added
Private Sub xlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
    MsgBox Sh.Name
End Sub

'-- Create a new module
'-- Following code goes there

Option Explicit

'/ A new instance  for the Class that we created.
Dim oGh As New clsGlobalHandler

'/ To start tracking sheet additions call this method first. Most likely in WorkBook_Open
'/ Once called any new sheet across the app insatnce will be intercepted.
Sub SetGlobalHandler()
    Set oGh.xlApp = Application
End Sub

'/ Call this to remove the global handler.
Sub ResetGlobalHandler()
    Set oGh.xlApp = Nothing
End Sub