Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: protecting a sheet

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to keep for deleting a sheet from a workbook, I have a sheet named "Data" that I don't want someone to accidentally delete, protecting the workbook is not a option, sheets need to be put in and deleted all the time, just want to make sure that the "data" sheet is not deleted. maybe something like before delet? Thanks for your help

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Protect just the "Data" sheet not the whole workbook.

  3. #3
    Guest

    Default

    protects the data, but won't prevent the sheet from being deleted.

    On 2002-03-10 16:06, SamS wrote:
    Protect just the "Data" sheet not the whole workbook.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes just the sheet named "Data" sheet not the whole workbook

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    should have tried it first - sounds like some vba code is needed in the workbook module, tried a few things but couldn't get it to work

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code will grey out the delete sheet option on all toolbars and also if you right click the sheet tab. Right click on the DATA sheet tab, choose VIEW CODE from that menu, and paste this code into the VBE. Thanks to Ivan for showing me the Control ID method of deleting menu items.

    Private Sub Worksheet_Activate()
    ID = 847
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = False
    Next
    End Sub

    Hope this helps

    Rick

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the code below makes it so you can't delete any of the sheets, I need it to work on only the one named "Data". can it be changed to do that?
    Private Sub Worksheet_Activate()
    ID = 847
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = False
    Next
    End Sub

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Paul:

    If you have put that code into the Workbook or into a module, it will work on all sheets (I believe--someone please correct me if I'm wrong).

    You need to double-click the Data sheet in the VB editor window and paste it THERE only. You'll need to remove it from the ThisWorkbook or from a module if it is there.

    Then test.

    Good luck.
    ~Anne Troy

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I put it in the worksheet code, but its worst that I thought now I can't delete ANY SHEET IN ANY WORKBOOK, how do I change it back. I have deleted the code saved the book closed excel, restarted excel, I still can't delete ANY sheets.Help!

  10. #10
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Paul


    Seems like this has caused grief for a number of Excel users. I recenlty answered this for another person here:

    http://mrexcel.com/board/viewtopic.p...1862&forum=2&2

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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