CODEWITHSUNDEEP
excelvba
Lim
Lim

Reputation: 75

Automatically run macro if the user inserted a new worksheet

I would like my macro to run automatically if it detects that the user inserted a new worksheet into the workbook (existing & new). 

 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

Upvotes: 0

Views: 227

Answers (1)

cyboashu
cyboashu

Reputation: 10433

As mentioned in the comments you need to handle WorkbookNewSheet at Application level.

'-- 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

Upvotes: 1

Related Questions