- Protect a Workbook
- VBA – Disable inserting new worksheets
- 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:
- Click the
Protect Workbookin the
- In the
Protect Structure and Windowsdialog box, check the
Structurebox and enter a password (optional).
- 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.
Alt+F11 (for Mac
⌘+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: