prevent a user from adding any sheet in a workbook
 

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

prevent a user from adding any sheet in a workbook
 

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 wrote:

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

prevent a user from adding any sheet in a workbook
 

On Apr 6, 8:14*pm, Dave Peterson wrote:
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 wrote:

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

Thanks. It worked fine.