Preventing Users from Inserting More 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.
The following code will get the job done:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Sorry, you cannot add any more sheets to this workbook", _
vbInformation
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.
Another way to prevent users from adding worksheets is to select Review → Changes → Protect Workbook, and then press the Protect Structure and Windows button (pre-2007, Tools → Protection → Protect Workbook..., ensure that the Structure checkbox is checked, and click OK). Excel's worksheet protection is a rather blunt instrument and will also prevent many other Excel features from working.