|
Prevent Sheet Deletion
Hi! Is it possible to prevent a particular sheet from being deleted? I have a workbook that contains sheets (of course). One of the sheets serves as a "Help" sheet. I don't want users to accidentally (or intentionally) delete just this sheet. -- T-容x ------------------------------------------------------------------------ T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401664 |
Prevent Sheet Deletion
you could protect the workbook. as long as users do not need to add or delete other sheets. they will be able to change the contents of sheets. or as a safety you could make a copy of the help sheet and hide it. -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=401664 |
Prevent Sheet Deletion
It doesn't look like there's a worksheet or workbook function to cover
that. Probably the best thing to do is to save a copy of the help sheet in a password protected file. In the workbook_open() event, iterate through the sheets and if the help sheet is missing, reload the help sheet from the other file. |
Prevent Sheet Deletion
Ctrl.OnAction = "RefuseToDelete"
Norman, that's great stuff. Where can I find a reference on all the OnAction codes? ---- Nick Hebb BreezeTree Software http://www.breezetree.com |
Prevent Sheet Deletion
Hi Nick,
Norman, that's great stuff. Where can I find a reference on all the OnAction codes? Look up the OnAction property in VBA help. If, as I suspect, you are in fact referring to the commandbar control numbers, you might like to download John Green's nice CBList addin which, amongst other things, lists all CommandBars It also provides a listing of all popup menus and their constituent elements. It is freely downloadable from Rob Bovey's Office automation site: http://www.oaltd.co.uk/MVP/Default.htm --- Regards, Norman "Nick Hebb" wrote in message ups.com... Ctrl.OnAction = "RefuseToDelete" Norman, that's great stuff. Where can I find a reference on all the OnAction codes? ---- Nick Hebb BreezeTree Software http://www.breezetree.com |
Prevent Sheet Deletion
Norman's code will stop users from deleting the sheet if the user doesn't
disable macros (and activates that sheet at least once) by stopping the menu commands. But I could still choose to move it to a new workbook--or even use code to delete that sheet. Just something to be aware of. On the other hand, workbook protection is easily broken, too. "T-容x" wrote: Hi! Is it possible to prevent a particular sheet from being deleted? I have a workbook that contains sheets (of course). One of the sheets serves as a "Help" sheet. I don't want users to accidentally (or intentionally) delete just this sheet. -- T-容x ------------------------------------------------------------------------ T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401664 -- Dave Peterson |
Prevent Sheet Deletion
And if you have multiple workbooks open and go from the Help sheet to a
different workbook, then the worksheet_deactivate event won't fire. You may want to add Workbook_Activate and Workbook_Deactivate code, too. (I like the workbook protection suggestion more and more <vbg.) Dave Peterson wrote: Norman's code will stop users from deleting the sheet if the user doesn't disable macros (and activates that sheet at least once) by stopping the menu commands. But I could still choose to move it to a new workbook--or even use code to delete that sheet. Just something to be aware of. On the other hand, workbook protection is easily broken, too. "T-容x" wrote: Hi! Is it possible to prevent a particular sheet from being deleted? I have a workbook that contains sheets (of course). One of the sheets serves as a "Help" sheet. I don't want users to accidentally (or intentionally) delete just this sheet. -- T-容x ------------------------------------------------------------------------ T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401664 -- Dave Peterson -- Dave Peterson |
Prevent Sheet Deletion
Thanks guys for all your suggestions!!! I can't protect the workbook as I must allow users to add/delete sheets (and do other stuffs not available in a protected workbook). :( Norman Jones, I'll give your code a try. Thanks. -- T-容x ------------------------------------------------------------------------ T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401664 |
| All times are GMT +1. The time now is 03:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com