Excel 2019

Workbook NewSheet Event

The NewSheet event occurs when a new sheet (chart sheet or worksheet) is created in the workbook. To listen this event, use the Workbook_NewSheet event listener procedure which has one Sh As Object argument. The argument is the new sheet added to the workbook.

To write event procedure, open a workbook and press Alt + F11 to open Visual Basic Editor (VBE). Then double-click the ThisWorkbook object in the Project Explorer tree to show its code module:
NewSheet Event

Workbook_NewSheet Examples:

The following example disallow the addition of any new worksheets. The Workbook_newSheet event procedure promptly deletes a new sheet as soon as it is added:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Application.DisplayAlerts = False
 Sh.Delete
 Application.DisplayAlerts = True
 MsgBox "Sheet deleted. New sheets are not allowed."
End Sub

What is Application.DisplayAlerts

Excel displays the following confirmation box when the user delete a worksheet:

But setting Application.DisplayAlerts to False disabled the prompt and alert. The above example delete the newly added worksheet and does not prompt the user to cancel the operation.

Place new sheet at the beginning:

Following example moves the new sheet at the beginning of the workbook:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Sh.Move before:=ThisWorkbook.Sheets(1)
End Sub

Determine the newly added sheet type

Because a new sheet can be either a worksheet or a chart sheet, the following example determines the sheet type:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 MsgBox TypeName(Sh)
End Sub

Above example will shows Worksheet message when you add a new worksheet in the workbook and Chart message when you move a chart to a sheet.

How to create a chart sheet

You can create a chart sheet by moving a chart to sheet, select chart, click Design tab > Move Chart and select New sheet from the Move Chart dialog box:
Move chart to a sheet

Advertisement:
Advertisement: