Disable New Worksheet Function in Excel

[TDugan]

I need to prevent users from adding new tabs to an Excel worksheet that I have.

I have added the following code to "ThisWorkbook".

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "New tabs are not allowed in the Plan Profile!", vbOKOnly, "New tabs not allowed"
Application.DisplayAlerts = False
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

End Sub

This works great if a user would try to add a new sheet once. However, I tested doing it again (to account for persistent people) and I get a Subscript out of Range error. I have decided that this is because Excel adds "Sheet 2" instead of "Sheet 1" the second time around and my code only references Sheet 1 which no longer exists at that point.

So I'm wondering if there is a way to disable adding new sheets altogether instead of stopping them after the fact.

Any tips?

Thanks for you help.

 

[strongm]

You'd be better off asking in forum707

 

[TDugan]

Thanks for the tip.

I posted it in 707 and already have a resolution to my issue.