Prevent Users From Deleting Worksheets

**Spencer** · 03-25-2011, 08:22 PM

I am trying to bombproof this spreadsheet that I have been working on to the point where it would be very difficult for my co-workers to mess it up.

I was suprised to find out that even though my worksheets are protected, that I can simply right-click on the worksheet tab and choose Delete.

I looked in both the Workbook and Worksheet event list and there is nothing there like "Before Delete", etc.

Is there anyway that I can prevent users from deleting Worksheets? If so, I would also like to prevent them from Inserting them as well.

I can't use the Protect Workbook Structure feature because it won't allow worksheet tabs to be renamed. I have VBA code that automatically renames the worksheet tab name based on the value of a cell in that worksheet and I need that functionality.

Thanks, Spence

**shg** · 03-26-2011, 12:16 AM

Your code can unprotect the workbook, do what it needs to do, and then reprotect it.

See Help for the Protect method.

**Mayweed** · 03-26-2011, 12:33 AM

I'm sure shg could clean this code up for you, workbook attached, passwords "asd"

**Spencer** · 03-26-2011, 12:40 AM

Originally Posted by shg

Your code can unprotect the workbook, do what it needs to do, and then reprotect it.

See Help for the Protect method.

Thanks, I didn't even think of trying that. I'll give that a shot tomorrow, as my brain is too burned out on VBA tonight.

You guys are just as amazing as Excel itself.

Thanks for all your help,
Spence

**Spencer** · 03-28-2011, 03:39 PM

Adding the code to unprotect and then reprotect the workbook seems to work fine. I don't know what else this will affect, but as far as the autmatic renaming of my worksheet tab names, it works great.

Thanks, Spence