CODEWITHSUNDEEP
excelexcel-2007vba
rohrl77
rohrl77

Reputation: 3337

How to prevent user from deleting sheet, but leave all else open

I have developed an Excel Application in which useres can enter custom categories. I let the user call up and hide this data entry worksheet with a button in the custom ribbon.

Now I've realized that the user can accidentally delete these worksheets. How do I disable the delete command for this worksheet while leaving all else open?

I've been searching the web but have come up empty on this.

This is for Excel 2007

Thanks

Upvotes: 1

Views: 15002

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Protect them.

Tools > Protection > Protect Worksheet.

Add the password and choose what actions your users should do in the sheets.

You can do the same using VBA too. Check the following link

Updated with a code for sheet level protect

You may put the following code in the sheet that you need to manage any mischief ;)

Private Sub Worksheet_Activate()
    ThisWorkbook.Protect Password:="Password", Structure:=True
End Sub

Private Sub Worksheet_Deactivate()
    ThisWorkbook.Unprotect Password:="Password"
End Sub

But you see, when you have a book with 100 sheets and if you want 50 sheets to be protected. Then you gotta either save all the sheet indices into a very hidden sheet. Usee that list in a module level VBA code to trigger the protect. Because not everytime you will have sheets in asceding order. If sheet indices in an order you can simply iterate them.

Let me know if you like to have workbook level code as well.

Upvotes: 2

Related Questions