Excel-VBA : Prevent Adding New Worksheet

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.


  • 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


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.

No New WorkSheet

No New WorkSheet


You may also like...

3 Responses

  1. Sef says:


    How about ways to prevent:
    a. copying a worksheet?
    b. renaming a worksheet?

    Much obliged!

  2. Eph says:

    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)

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: