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.

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

ToolsProtectionProtect Workbook.

Checkmark "structure".


Gord Dibben MS Excel MVP

On Fri, 25 May 2007 11:28:01 -0700, Trader_in_Paradise
wrote:

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.

"Gord Dibben" wrote:

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

ToolsProtectionProtect Workbook.

Checkmark "structure".


Gord Dibben MS Excel MVP


Thanks, Gord,

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.

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??

Trader_in_Paradise wrote:

"Gord Dibben" wrote:

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

ToolsProtectionProtect Workbook.

Checkmark "structure".


Gord Dibben MS Excel MVP


Thanks, Gord,

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.


--

Dave Peterson

"Dave Peterson" wrote:

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,

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.

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



Trader_in_Paradise wrote:

"Dave Peterson" wrote:

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,

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.

--

Dave Peterson

http://tutorialway.com/protection-in...eets-workbook/
Helpful site