Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to prevent adding sheet in a workbook. | Excel Programming | |||
Prevent user from saving workbook | Excel Discussion (Misc queries) | |||
prevent user from selecting and changing a sheet | Excel Programming | |||
prevent user to rename sheet | Excel Programming | |||
How to Prevent User from Unhiding Sheet | Excel Programming |