Delete A Sheet VB - Follow-Up

[chicagodiceman]

The following post Delete A Sheet VB made me curious whether there is an ability to disable deleting a sheet. Is it possible to disable deleting a sheet or is this something that cannot be coded for? I have a user that if there's a way, she'll find it to crash a very involved and highly coded workbook. Besides what I've learned from Mr. Excel posts on the subject, she's the next best source of gaining knowledge on error trapping.

Thanks in advance.

 

[Richard Schollar]

You could always Protect the Workbook.

Richard

 

[chicagodiceman]

Richard,

Thanks for the reply. True, that would have been an excellent option during the initial design of this particular workbook. But it has evolved over the 5 years we've been using it into a rather horrible beast. My concern with workbook protection would be how much time it would take to set-up the "unprotected" areas of the worksheets. I have a lot of little hidden cells scattered about to store values for the next time it is opened, etc. Ideally, I should start from scratch, re-code the entire thing and use my much improved VBA skills (thanks mostly to Mr. Excel and Lisa's ability to crash this project). I was just hoping there was an ability to disable the delete sheet option without too much additional work. This department no longer reports to me and the new Manager can't even spell Excel much less use it.

I may look into trying protection and seeing what effect it has on all of the various code currently included in the book.

Thanks again.

 

[Richard Schollar]

Rather than Sheet protection, I did mean the Tools>Protection>Protect Workbook and select to Protect Structure option - this won't stop anyone from Hiding/unhiding individual cells/columns etc or inputting values into cells etc but it will stop people adding/deleting new sheets (or even moving them). It isn't perfect (because of the above) and could 'get in the way' if being able to do the above is important.

Richard

 

[chicagodiceman]

That's the ticket. Once again, thank you for the help.