Prevent user from Inserting new worksheet

[DocBrown]

What is the approach I could use to prevent the user from adding new blank
worksheets using the Insert.. Worksheet... function? I've found the
Workbook_NewSheet event but this fires after the sheet has been created.

I have a command button on the sheet that creates the new sheets for the
user by copying a hidden template sheet. This macro does not trigger the
NewSheet event.

In the Workbook_NewSheet event, I can call the delete method:

ActiveWorkbook.Sheets(Sh.Name).Delete

but this generates the "Data may exist in sheet(s)... Warning. How do I
supress this?
Thanks

 

[Mike H]

Hi,

This will prevent anyone adding a sheet

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Sh.Delete
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
MsgBox "Adding Sheets isn't allowed"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

I just realised you will want to bypass this if a sheet is inserted with
your button so include this as the first line of the sub

If Parent.Caller = "Button 1" Then Exit Sub

Change Button 1 to the name of you button
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Gord Dibben]

I would be tempted to simply protect the workbook to prevent users from
inserting new sheets without running your macro.

In your macro unprotect, insert a sheet, then reprotect.

ActiveWorkbook.Unprotect Password:="justme"
'your insert hidden template sheet code here
ActiveWorkbook.Protect Password:="justme", Structure:=True, Windows:=True

Second point.................

To ignore the warning when deleting sheets

application.displayalerts = false
ActiveWorkbook.Sheets(Sh.Name).Delete
application.displayalerts = true

But if you follow the first recommendation you won't need that.


Gord Dibben MS Excel MVP

 

[DocBrown]

Thanks Guys,

Trying out your suggestions yielded interesting results.
1. When you invoke the Inset.. Worksheet or the 'newsheet' tab at the
bottom. Excel adds the blank sheet before the Worksheet_NewSheet event is
called.
2. The key for me was to remember about the .DisplayAlerts = false.

So my final macro is this:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

' By the time we get called Excel has already created a blank
' worksheet. So, let's delete it.
Sh.Delete

' Invoke the same function as the New Worksheet button does.
CopyTemplate

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

So now, the only kind of ws the user can create is the one I provided.
Thanks for the reminder.