prevent a user from adding any sheet in a workbook

[Subodh]

I have an excel workbook in which i don't want the user to add any
sheets.
I tried following code to delete the sheet every time a new sheet is
inserted.
However, Microsoft generates a message and user has to select Delete
key every time
and if the user presses cancel then a sheet is inserted.
How can i automatically make the Delete selection so that user cannot
insert aany sheets.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Delete
End Sub

 

[Dave Peterson]

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End Sub


But you may want to just protect the workbook's structure.
Tools|Protection|protect workbook
is where you'd find it in xl2003 menus.

It prohibits the user from adding/deleting/changing the order/renaming of the
sheets.

This password (like most passwords) are easily cracked.

On the other hand, the code can be bypassed by disallowing macros to run -- or
just disabling events.

So nothing is really foolproof.

 

[Subodh]

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    Sh.Delete
    Application.DisplayAlerts = True
End Sub

But you may want to just protect the workbook's structure.
Tools|Protection|protect workbook
is where you'd find it in xl2003 menus.

It prohibits the user from adding/deleting/changing the order/renaming ofthe
sheets.

This password (like most passwords) are easily cracked.

On the other hand, the code can be bypassed by disallowing macros to run -- or
just disabling events.

So nothing is really foolproof.

Thanks. It worked fine.