时间戳

首页 > 解决方案 > 如果用户插入了新工作表，则自动运行宏

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

问题描述

如果我的宏检测到用户将新工作表插入工作簿（现有和新），我希望它自动运行。

 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

标签： excelvba

解决方案

如评论中所述，您需要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

推荐阅读