Excel-VBA : Prevent Adding New Worksheet

by

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

Select ThisWorkBook
Select ThisWorkBook
  • Now select the “Workbook” from the first drop down on the right
select workbook
select workbook
  • Select the NewSheet from the second drop down on the right
Select NewSheet
Select NewSheet

 

  • Now you will see the function Private Sub Workbook_NewSheet(ByVal Sh As Object).
WorkBook_NewSheet function
WorkBook_NewSheet function
  • Add the following code in the function.

Application.DisplayAlerts = False

ActiveSheet.Delete

MsgBox “Sorry, Adding new Sheet is not allowed”

 

code
code

And thats it you are done. Lets test our program.

Go to excel and try adding a new worksheet.

No New WorkSheet
No New WorkSheet

 

Related Posts:

3 thoughts on “Excel-VBA : Prevent Adding New Worksheet”

  2. 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)

    Reply

Leave a Reply to Sef Cancel reply