prevent deleting worksheet, set as xlVeryHidden enstead

**MagicWIU** · 02-18-2009, 08:36 AM

I would like to "Archive" any worksheet that has data beyond row 14 when a user tries to delete the sheet.

I know in the ThisWorkbook code, I can edit what happens when a worksheet is added, by editing the sub "Workbook_NewSheet" and I was wondering if there is a way to do something similar for when sheets are deleted.

I know, usually, when a sheet is deleted, a confirmation message is shown, asking if the user is sure they would like to delete the sheet. I would like to avoid showing that message, and show my own, asking if the user would like to "Archive" the sheet enstead. If they select yes, I would like set the sheet as xlveryhidden, and rename it to nameArchived. If no is selected, then delete message may be shown.

Any suggestions?

**royUK** · 02-18-2009, 08:42 AM

I don't believe there is a delete event that you can use. If you open the VB Editor, click on ThisWorkBook & set the left hand drop down to WorkBook you can see a list of events available

**MagicWIU** · 02-18-2009, 09:10 AM

hahaha thanks, this may have helped... after checking what you said... i noticed Sheetdeactivate.... is one of the options. Under excel's help is this little code:

Please Login or Register  to view this content.

which shows a message whenever a sheet is deactivated. It also happens to show the message right before a sheet is deleted.

This may help, but it still doesn't solve my question. I don't really want my archive message showing every time someone views a new worksheet.
Any suggestions on limiting the message to only deleted workshets?

**MagicWIU** · 02-19-2009, 07:17 PM

BUMP Not solved yet

**shg** · 02-19-2009, 07:27 PM

Protect the workbook (which prohibits deleting sheets) then add a macro that archives the sheet.

**MagicWIU** · 02-20-2009, 03:15 AM

I thought briefly about protecting the workbook, but I still want the user to be able to delete it, if necessary.

I would like the user to be able to click the same "delete worksheet" buttons or right clicks, I just want to catch it before its deleted.

Is there any way to force the cancel button to be pressed when a user deletes a worksheet? If there were, I could run a macro at that point...

any ideas?

**MagicWIU** · 02-27-2009, 07:32 AM

Was unsuccessful with this for days. I talked to a friend about it and he found an answer here

http://www.mrexcel.com/forum/showthr...86#post1855786

there are a few methods shown. First one that worked was hiding the command button for deleting a worksheet. Then creating a duplicate command button with your own macro and placing the button in the same place as the original button. This method works very well on previous versions of office.

In 07 The ribbons are used instead of the original menus therefore the first method would not work with the command buttons within the ribbons. So there is a workaround for 07. The link above has all the information you would need if you were trying to do something similar to my project.

Enjoy