Code to Prevent Adding Worksheets

[Guest]

I am trying to prevent a user from adding worksheets to a particular
workbook. The code I am using (see below) doesn't work; I don't get any
error messages. Any suggesions on what I am doing wrong?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "New sheets may not be added. " & _
"Sheet will be deleted"
Sh.Delete
Application.DisplayAlerts = True
End Sub

Thanks...Paige

 

[Ron de Bruin]

Hi Paige

Why don't you protect the workbook

 

[Rob Bovey]

I am trying to prevent a user from adding worksheets to a particular
workbook. The code I am using (see below) doesn't work; I don't get any
error messages. Any suggesions on what I am doing wrong?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "New sheets may not be added. " & _
"Sheet will be deleted"
Sh.Delete
Application.DisplayAlerts = True
End Sub

Hi Paige,

Your code seems to work OK for me here. But wouldn't it be easier to
just protect the workbook? That would prevent the user from adding sheets
without any code at all.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Myrna Larson]

Hmmm.... your code works fine for me. What do you mean when you say it doesn't
work? Do not see the message box? If so, are you sure that events are enabled?
You could add the line

Application.EnableEvents = True

to the Workbook_Open event code.

But have you considered protecting the workbook's structure? That disallows
adding sheets (and possibly some other things). Check it out in Help.