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:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox Sh.Name
End Sub

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?