Is there a way to prevent a userfrom deleting a single spreadsheet? I don't
want to lock down the whole workbook, but I would like to protect one
worksheet in the workbook. I was looking for something like:

Sub Worksheet_BeforeDelete (lgDelete as Boolean)

But of course, no such option exist. Any help will be greatly appreciated.

If you don't want to use workbook protection, then I don't think you can stop it
or catch it.

But you could use some other event to (Workbook_SheetSelectionChange???) to look
for it. If it's gone, give that user a severe chastising.

Or ...maybe... keep a hidden copy of the worksheet and just recreate that just
deleted worksheet. (This may not help--if you have formulas in other sheets
that refer to that worksheet, it'll be too late.)



JR_06062005 wrote:

Is there a way to prevent a userfrom deleting a single spreadsheet? I don't
want to lock down the whole workbook, but I would like to protect one
worksheet in the workbook. I was looking for something like:

Sub Worksheet_BeforeDelete (lgDelete as Boolean)

But of course, no such option exist. Any help will be greatly appreciated.

--

Dave Peterson

Your idea sounds promising, esepecially about recreating the sheet. It
doesn't have any connecting formulas. In fact all it is is a message for
users when they disable the macro feature. The workbook is locked and the
only spreadsheet visible is the one with the message. However, when macros
are enabled, there is nothing to keep the curious user from making the
message spreadsheet visible and deleting it.

So I have a follow-up question. Is there a way to see if a particular
worksheet exist? On the Workbook Before Close event, I could test to sheet
if the worksheet exist and if not, recreate it.

Thanks for your suggestions.

"Dave Peterson" wrote:

If you don't want to use workbook protection, then I don't think you can stop it
or catch it.

But you could use some other event to (Workbook_SheetSelectionChange???) to look
for it. If it's gone, give that user a severe chastising.

Or ...maybe... keep a hidden copy of the worksheet and just recreate that just
deleted worksheet. (This may not help--if you have formulas in other sheets
that refer to that worksheet, it'll be too late.)



JR_06062005 wrote:

Is there a way to prevent a userfrom deleting a single spreadsheet? I don't
want to lock down the whole workbook, but I would like to protect one
worksheet in the workbook. I was looking for something like:

Sub Worksheet_BeforeDelete (lgDelete as Boolean)

But of course, no such option exist. Any help will be greatly appreciated.

--

Dave Peterson

First, I wouldn't use the _beforeclose event. The workbook would have to saved
to include that change--and if the user actually wanted to discard other
changes, you might be messing them up.

But you could use workbook_beforesave, maybe...

dim wks as worksheet
set wks = nothing
on error resume next
set wks = me.worksheets("warningmessagesheet")
on error goto 0

if wks is nothing then
'recreate it
else
'already there
end if



JR_06062005 wrote:

Your idea sounds promising, esepecially about recreating the sheet. It
doesn't have any connecting formulas. In fact all it is is a message for
users when they disable the macro feature. The workbook is locked and the
only spreadsheet visible is the one with the message. However, when macros
are enabled, there is nothing to keep the curious user from making the
message spreadsheet visible and deleting it.

So I have a follow-up question. Is there a way to see if a particular
worksheet exist? On the Workbook Before Close event, I could test to sheet
if the worksheet exist and if not, recreate it.

Thanks for your suggestions.

"Dave Peterson" wrote:

If you don't want to use workbook protection, then I don't think you can stop it
or catch it.

But you could use some other event to (Workbook_SheetSelectionChange???) to look
for it. If it's gone, give that user a severe chastising.

Or ...maybe... keep a hidden copy of the worksheet and just recreate that just
deleted worksheet. (This may not help--if you have formulas in other sheets
that refer to that worksheet, it'll be too late.)



JR_06062005 wrote:

Is there a way to prevent a userfrom deleting a single spreadsheet? I don't
want to lock down the whole workbook, but I would like to protect one
worksheet in the workbook. I was looking for something like:

Sub Worksheet_BeforeDelete (lgDelete as Boolean)

But of course, no such option exist. Any help will be greatly appreciated.

--

Dave Peterson


--

Dave Peterson