Be the first user to complete this post
|
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](/static/media/excel/2014/10/Select-ThisWorkBook.jpg)
- Now select the "Workbook" from the first drop down on the right
![select workbook](/static/media/excel/2014/10/select-workbook.png)
- Select the NewSheet from the second drop down on the right
![Select NewSheet](/static/media/excel/2014/10/Select-NewSheet.png)
- Now you will see the function Private Sub Workbook_NewSheet(ByVal Sh As Object).
![WorkBook_NewSheet function](/static/media/excel/2014/10/WorkBook_NewSheet-function.jpg)
- Add the following code in the function.
Application.DisplayAlerts = False
ActiveSheet.Delete
MsgBox "Sorry, Adding new Sheet is not allowed"
![code](/static/media/excel/2014/10/code1.jpg)
And thats it you are done. Lets test our program.
Go to excel and try adding a new worksheet.
![No New WorkSheet](/static/media/excel/2014/10/No-New-WorkSheet.jpg)
Also Read:
- Excel-VBA : Send a Mail using Predefined Template From MS Outlook Using Excel
- VBA-Excel: Open word document using GetObject()
- VBA-Excel: Writing Text to Word document
- VBA-Excel: Create worksheets with Names in Specific Format/Pattern.
- VBA-Excel: Create a WorkBook at Runtime.