Many a times you need a scenario where you dont want user to add new worksheets in you Excel work book.
Its quite easy to do.
Steps:
- Go to VBA Editor
- Expand your VBA Project
- Double Click ThisWorkBook

- Now select the “Workbook” from the first drop down on the right

- Select the NewSheet from the second drop down on the right

- Now you will see the function Private Sub Workbook_NewSheet(ByVal Sh As Object).

- Add the following code in the function.
Application.DisplayAlerts = False
ActiveSheet.Delete
MsgBox “Sorry, Adding new Sheet is not allowed”

And thats it you are done. Lets test our program.
Go to excel and try adding a new worksheet.

Nice!
How about ways to prevent:
a. copying a worksheet?
b. renaming a worksheet?
Much obliged!
Please read http://excel-macro.tutorialhorizon.com/excel-vba-prevent-changing-the-worksheet-name/
soon i will post on prevent user to copy a worksheet.
Thanks
Sumit
This can also be done outside of VBA by protecting workbooks (the actual functions are grayed out, no dialog box needed) but it doesn’t allow for more granular control. (e.g., allow adding new worksheets but not deleting worksheets)