Disable New Worksheet Function

[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 your help.

 

[PHV]

What about this ?
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
[!]Sh[/!].Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Or...:

Dim i As Integer

MsgBox "New tabs are not allowed in the Plan Profile!", vbOKOnly, "New tabs not allowed"
Application.DisplayAlerts = False
For i = Sheets.Count To 1 Step -1
    If InStr("[blue]*Sheet2*Sheet5*[/blue]", "*" & Sheets(i).Name & "*") = 0 Then
        Sheets(i).Delete
    End If
Next i
Application.DisplayAlerts = True

In this example you want to keep Sheet2 and Sheet5
Change it to whatever sheets you want to keep

Have fun.

---- Andy

 

[SkipVought]

But, of course, if a user opens the workbook but fails to enable macros, they will still be able to add whatever sheets they like.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TDugan]

Thanks PH. I appreciate your quick response!

Tested it out and your code works perfectly!

I have also received a tip of the following:

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
ActiveSheet.Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

End Sub

Tested this also and it works too! Thanks again.

 

[SkipVought]

ActiveSheet.Select     'the ActiveSheet is the Selected Sheet!
ActiveWindow.SelectedSheets.Delete

This is a redundency, like saying, "Selected the selected item." it is already selected!

This would be much more succinct...

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[b]
  Sh.Delete[/b]
  Application.DisplayAlerts = True
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveInIowa]

Is protecting the workbook structure too restrictive (Review tab -> Protect Workbook button)?