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:
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
toFalse
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: