Be the first user to complete this post

  • 0
Add to List

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.

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



Also Read:

  1. VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet
  2. FileSystemObject:OpenTextFile Method
  3. VBA-Excel - Merger - Merge or Combine Many Word Documents Into One
  4. VBA-Excel: Working with Bookmarks- Insert text before Bookmark
  5. VBA-Excel: WorkBook.Save Method
  6. VBA-Excel: Convert Numbers (Rupees) into Words OR Text - Updated Till 1000000 Crore With Decimal Numbers
  7. Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
  8. VBA Excel - Cells, Ranges and Offset: Refer Range by using A1 Notations