Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
wrote in message
oups.com... I need some help with writing a macro which can prevent th euser to add additional sheets in a workbook. Why not just protect the workbook. Tools/Protection/Protect Workbook from the Excel menu. This will prevent any new sheets from being added. -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
Generally I agree with Rob that the best solution is probably to protect the
workbook. If however you want to proceed this code will prevent new sheets from being added. It does not however stop someone from copying sheets into the book. This code goes in the ThisWorkbook module. Right clickt the Excel icon next to File on the menu and select view code. Add this code... Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "Sorry. No adding new sheets." Sh.Delete Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson " wrote: I need some help with writing a macro which can prevent th euser to add additional sheets in a workbook. thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
Just some added thoughts.
Rob's method could of course could be easily defeated through overt action by the user to break the protection. Jim's method (like any software based protection) could be inadvertently defeated by setting security to high and the user would never know you cared whether worksheets were added or not. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... Generally I agree with Rob that the best solution is probably to protect the workbook. If however you want to proceed this code will prevent new sheets from being added. It does not however stop someone from copying sheets into the book. This code goes in the ThisWorkbook module. Right clickt the Excel icon next to File on the menu and select view code. Add this code... Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "Sorry. No adding new sheets." Sh.Delete Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson " wrote: I need some help with writing a macro which can prevent th euser to add additional sheets in a workbook. thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
"Tom Ogilvy" wrote in message
... Just some added thoughts. Rob's method could of course could be easily defeated through overt action by the user to break the protection. Jim's method (like any software based protection) could be inadvertently defeated by setting security to high and the user would never know you cared whether worksheets were added or not. Yes, of course. Clearly, applying a strong file-open password to the workbook and then not supplying the user with that password would be a more robust method of preventing new worksheets from being added. ;-) -- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
I was on your side.
-- Regards, Tom Ogilvy "Rob Bovey" wrote in message ... "Tom Ogilvy" wrote in message ... Just some added thoughts. Rob's method could of course could be easily defeated through overt action by the user to break the protection. Jim's method (like any software based protection) could be inadvertently defeated by setting security to high and the user would never know you cared whether worksheets were added or not. Yes, of course. Clearly, applying a strong file-open password to the workbook and then not supplying the user with that password would be a more robust method of preventing new worksheets from being added. ;-) -- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to prevent adding sheet in a workbook.
"Tom Ogilvy" wrote in message
... I was on your side. No worries Tom, I was just pulling your chain. <g We both know that between determined fools and malicious experience very little in Excel can ever be made bulletproof. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent users to use a workbook/sheet after a specified day | Excel Worksheet Functions | |||
need to modify a macro to prevent it from executing if in wrong area of the sheet | Excel Discussion (Misc queries) | |||
Can I prevent a sheet from moving within a workbook? | Excel Programming | |||
Can I prevent a sheet from moving within a workbook? | Excel Programming | |||
How to prevent pasting to every sheet in workbook | Excel Programming |