Protect an Excel WorkSheet from being deleted

T

Trader_in_Paradise

I have a blank WorkSheet called "Scratch" in an Excel workbook with lots of
VB code.

I want the user to be able to do anything with the blank WorkSheet called
"Scratch" except to Rename it or to Delete it.

Stumped. Please help. Thank you.
 
G

Gord Dibben

You must protect the workbook to prevent deleting or renaming a sheet.

Tools>Protection>Protect Workbook.

Checkmark "structure".


Gord Dibben MS Excel MVP
 
T

Trader_in_Paradise

Gord Dibben said:
You must protect the workbook to prevent deleting or renaming a sheet.

Tools>Protection>Protect Workbook.

Checkmark "structure".


Gord Dibben MS Excel MVP


Thanks, Gord,
Click to expand...

I still would like to find a way to protect just one named worksheet in a
workbook from being deleted. Is there a way to trap the Woeksheet Delete
event for that particular worksheet? Thank you.
 
D

Dave Peterson

There is no Worksheet_delete (or worksheet_beforedelete or
worksheet_afterdelete) event.

Have you thought about protecting the workbook, then giving the user a macro
that would delete any sheet except for the one(s) you don't want deleted??
 
T

Trader_in_Paradise

Dave Peterson said:
There is no Worksheet_delete (or worksheet_beforedelete or
worksheet_afterdelete) event.

Have you thought about protecting the workbook, then giving the user a macro
that would delete any sheet except for the one(s) you don't want deleted??


Thanks, Dave,
Click to expand...

I already have two hidden sheets. I also have a VBA routine behind a Delete
Button that allows the user to delete a sheet, except if the sheet is named
Options or Date and as long as the sheet desired to delete is not the only
visible sheet. I did not know I could protect the workbook and still allow
sheets to be deleted.

I will try your suggestion in my code tomorrow. Thanks again.
 
D

Dave Peterson

In your code, you'll have to check the configuration, unprotect the workbook,
delete the sheet, reprotect the workbook.
 
Top