Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

Excel-VBA : Prevent Adding New Worksheet

Many a times you need a sce­nario where you dont want user to add new work­sheets in you Excel work book.

Its quite easy to do.

Steps:

  • Go to VBA Editor
  • Expand your VBA Project
  • Dou­ble Click This­Work­Book

Select ThisWorkBook

Select This­Work­Book

  • Now select the “Work­book” from the first drop down on the right
select workbook

select work­book

  • Select the New­Sheet from the sec­ond drop down on the right
Select NewSheet

Select New­Sheet

 

  • Now you will see the func­tion Pri­vate Sub Workbook_NewSheet(ByVal Sh As Object).
WorkBook_NewSheet function

WorkBook_NewSheet func­tion

  • Add the fol­low­ing code in the function.

Application.DisplayAlerts = False

ActiveSheet.Delete

Msg­Box “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

 

You may also like...

3 Responses

  1. Sef says:

    Nice!

    How about ways to pre­vent:
    a. copy­ing a work­sheet?
    b. renam­ing a worksheet?

    Much obliged!

  2. Eph says:

    This can also be done out­side of VBA by pro­tect­ing work­books (the actual func­tions are grayed out, no dia­log box needed) but it doesn’t allow for more gran­u­lar con­trol. (e.g., allow adding new work­sheets but not delet­ing worksheets)

Leave a Reply

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

%d bloggers like this: