Excel

Application WorkbookNewChart, WorkbookNewSheet & NewWorkbook Events

In this tutorial, you’ll learn how to use WorkbookNewChart, WorkbookNewSheet and NewWorkbook events. These are the application-level events, WorkbookNewChart triggered when a new chart sheet added in any workbook, WorkbookNewSheet triggered when a new sheet added in any Excel workbook and NewWorkbook event triggered when you open a new (blank) workbook in Excel. Let’s open VBE (press Alt+F11) and write the following code in ThisWorkbook code module. This code enables your workbook to listen to application object events (for details, visit how to capture application events):

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

WorkbookNewChart

Syntax: App_WorkbookNewChart(Wb, Ch)

The WorkbookNewChart event occurs when a new chart added to any workbook. The App_WorkbookNewChart procedure has two arguments:

  1. Wb
    The Workbook
  2. Ch
    The newly added chart

App_WorkbookNewChart Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookNewChart(ByVal Wb As Workbook, ByVal Ch As Chart)
 MsgBox "Application WorkbookNewChart"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookNewChart is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_NewChart procedure.

WorkbookNewSheet

Syntax: App_WorkbookNewSheet(Wb, Sh)

The WorkbookNewSheet event occurs when a new sheet added to any workbook. The App_WorkbookNewSheet has two arguments:

  1. Wb is the workbook
  2. Sh is the new sheet

App_WorkbookNewSheet Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
 MsgBox TypeName(Sh) & " added in " & Wb.Name
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookNewSheet is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_NewSheet procedure.

NewWorkbook

Syntax: App_NewWorkbook(Wb)

The NewWorkbook event occurs when a new workbook is created in Excel application. App_NewWorkbook procedure has one argument (Wb) which represents the newly created workbook

App_NewWorkbook Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
 MsgBox "Another workbook has been created"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub