Prevent Sheet Deletion

Hi!

Is it possible to prevent a particular sheet from being deleted?

I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.

>>Ctrl.OnAction = "RefuseToDelete"

Norman, that's great stuff. Where can I find a reference on all the
OnAction codes?

----
Nick Hebb
BreezeTree Software
http://www.breezetree.com

Hi Nick,

> Norman, that's great stuff. Where can I find a reference on all the
> OnAction codes?

Look up the OnAction property in VBA help.

If, as I suspect, you are in fact referring to the commandbar control
numbers, you might like to download John Green's nice CBList addin which,
amongst other things, lists all CommandBars It also provides a listing of
all popup menus
and their constituent elements.

It is freely downloadable from Rob Bovey's Office automation site:


http://www.oaltd.co.uk/MVP/Default.htm



---
Regards,
Norman



"Nick Hebb" <n.hebb@comcast.net> wrote in message
news:1125742872.763112.41000@g43g2000cwa.googlegroups.com...
>>>Ctrl.OnAction = "RefuseToDelete"
>
> Norman, that's great stuff. Where can I find a reference on all the
> OnAction codes?
>
> ----
> Nick Hebb
> BreezeTree Software
> http://www.breezetree.com
>

It doesn't look like there's a worksheet or workbook function to cover
that. Probably the best thing to do is to save a copy of the help sheet
in a password protected file. In the workbook_open() event, iterate
through the sheets and if the help sheet is missing, reload the help
sheet from the other file.

Hi T-容x,

Try pasting the following two event procedures into the Help sheet module:

'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub

'-----------------------------------

Private Sub Worksheet_Deactivate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then Ctrl.OnAction = ""
Next
End Sub
'<<==========================

The above represents worksheet event code and should be pasted into the Help
sheets's code module (not a standard module and not the workbook's
ThisWorkbook module):

************************************************************
Right-click the Help sheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
************************************************************

Now paste the following sub into a standard module:

'==========================>>
Public Sub RefuseToDelete()

MsgBox "This help sheet should not be deleted!", _
Buttons:=vbExclamation, _
Title:="Cannot Deelete Help!"

End Sub

'==========================>>


---
Regards,
Norman



"T-容x" <T-ex.1urzmb_1125738324.8531@excelforum-nospam.com> wrote in message
news:T-ex.1urzmb_1125738324.8531@excelforum-nospam.com...
>
> Hi!
>
> Is it possible to prevent a particular sheet from being deleted?
>
> I have a workbook that contains sheets (of course). One of the sheets
> serves as a "Help" sheet. I don't want users to accidentally (or
> intentionally)
> delete just this sheet.
>
>
> --
> T-容x
> ------------------------------------------------------------------------
> T-容x's Profile:
> http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401664
>

Norman's code will stop users from deleting the sheet if the user doesn't
disable macros (and activates that sheet at least once) by stopping the menu
commands.

But I could still choose to move it to a new workbook--or even use code to
delete that sheet.

Just something to be aware of.

On the other hand, workbook protection is easily broken, too.

"T-容x" wrote:
>
> Hi!
>
> Is it possible to prevent a particular sheet from being deleted?
>
> I have a workbook that contains sheets (of course). One of the sheets
> serves as a "Help" sheet. I don't want users to accidentally (or
> intentionally)
> delete just this sheet.
>
> --
> T-容x
> ------------------------------------------------------------------------
> T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401664

--

Dave Peterson

And if you have multiple workbooks open and go from the Help sheet to a
different workbook, then the worksheet_deactivate event won't fire.

You may want to add Workbook_Activate and Workbook_Deactivate code, too.

(I like the workbook protection suggestion more and more <vbg>.)

Dave Peterson wrote:
>
> Norman's code will stop users from deleting the sheet if the user doesn't
> disable macros (and activates that sheet at least once) by stopping the menu
> commands.
>
> But I could still choose to move it to a new workbook--or even use code to
> delete that sheet.
>
> Just something to be aware of.
>
> On the other hand, workbook protection is easily broken, too.
>
> "T-容x" wrote:
> >
> > Hi!
> >
> > Is it possible to prevent a particular sheet from being deleted?
> >
> > I have a workbook that contains sheets (of course). One of the sheets
> > serves as a "Help" sheet. I don't want users to accidentally (or
> > intentionally)
> > delete just this sheet.
> >
> > --
> > T-容x
> > ------------------------------------------------------------------------
> > T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572
> > View this thread: http://www.excelforum.com/showthread...hreadid=401664
>
> --
>
> Dave Peterson

--

Dave Peterson

Thanks guys for all your suggestions!!!
I can't protect the workbook as I must allow users to add/delete sheets (and do other stuffs not available in a protected workbook).
Norman Jones, I'll give your code a try. Thanks.

I used the above code to share an excel file named "Query" on a Local Area Network".
Now my job is done.
But now I am not able to delete any sheet even when I disable macros.
I cannot even delete any sheet from a new workbook.
I have erased the code but still I get the following error message "Query.xls not found"
Please help!!!!!!!!!!