Closed Thread
Results 1 to 11 of 11

Prevent Sheet Deletion

  1. #1
    Registered User
    Join Date
    08-24-2005
    Location
    Philippines
    Posts
    75

    Question 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.

  2. #2
    Registered User
    Join Date
    09-16-2003
    Location
    Waiau Pa NZ
    Posts
    73
    you could protect the workbook.
    as long as users do not need to add or delete other sheets.
    they will be able to change the contents of sheets.

    or as a safety you could make a copy of the help sheet and hide it.
    Greetings from New Zealand
    Bill Kuunders

  3. #3
    Nick Hebb
    Guest

    Re: Prevent Sheet Deletion

    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.


  4. #4
    Norman Jones
    Guest

    Re: Prevent Sheet Deletion

    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
    >




  5. #5
    Nick Hebb
    Guest

    Re: Prevent Sheet Deletion

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


  6. #6
    Norman Jones
    Guest

    Re: Prevent Sheet Deletion

    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
    >




  7. #7
    Dave Peterson
    Guest

    Re: Prevent Sheet Deletion

    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

  8. #8
    Dave Peterson
    Guest

    Re: Prevent Sheet Deletion

    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

  9. #9
    Registered User
    Join Date
    08-24-2005
    Location
    Philippines
    Posts
    75

    Talking Thanks!!!

    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.

  10. #10
    Registered User
    Join Date
    12-20-2008
    Location
    Mumbai
    MS-Off Ver
    Microsoft Office 2003 and 2007
    Posts
    36

    Re: Prevent Sheet Deletion

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

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    32,055

    Re: Prevent Sheet Deletion

    Please don't post in 4-year old threads; start your own.

    See Help for Features that are unavailable in shared workbooks.
    Microsoft MVP - Excel
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts