Preventing Users from Inserting More Worksheets

1. Protect a Workbook
2. VBA – Disable inserting new worksheets
3. VBA – Limit the number of worksheets in a workbook

Protect a Workbook

If you want to prevent users from inserting more worksheets into a workbook, you can use the Protect Workbook command in Excel. This command locks the structure of the workbook, which prevents adding, deleting, hiding, or moving worksheets. To protect a workbook, follow these steps:

1. Click the Review tab.
2. Click Protect Workbook in the Protect group.
3. In the Protect Structure and Windows dialog box, check the Structure box and enter a password (optional).
4. Click OK and confirm the password.

Now, users will not be able to insert more worksheets into the workbook unless they know the password.

To remove the protection, click the Review tab, click Protect Workbook, if prompted type the password in the Password field, and click OK.

VBA Code to Prevent Users from Inserting Worksheets

Excel lets you protect a workbook’s structure so that users cannot delete worksheets, rearrange the order in which they appear, rename them, and so forth. Sometimes, though, you want to prevent just the addition of more worksheets, while still allowing other structural alterations.

Press Alt+F11 (for Mac ⌘+F11, or ⌘+Opt+F11) to open the Visual Basic Editor, double ThisWorkbook object in the Project Explorer to open its code window, and write the following code into the ThisWorkbook code editor:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
  Application.DisplayAlerts = False
  MsgBox "You cannot add any more sheets to this workbook"
  Sh.Delete
  Application.DisplayAlerts = True
End Sub

The code first displays the message box with the message and then immediately deletes the newly added sheet when the user clicks OK from the message box. The use of Application.DisplayAlerts = False stops the standard Excel warning that asks users if they really want to delete the sheet. With this in place, users will be unable to add more worksheets to the workbook.

Limit the number of worksheets in a workbook

If you want to limit the number of worksheets in a workbook, use the following VBA code, it prevents users from inserting more than five worksheets in the workbook:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Dim totalSheets As Long
 Dim maxSheets As Long

 maxSheets = 5 'replace with your own number

 totalSheets = Application.Worksheets.Count
 If totalSheets > maxSheets Then
  Application.DisplayAlerts = False
  Sh.Delete
  Application.DisplayAlerts = True
  MsgBox "You cannot insert more than " & maxSheets & " worksheets."
 End If
End Sub

Follow this link to learn more about the Workbook_NewSheet.

---

Understanding Workbooks and Worksheets: