Prevent Deleting Sheet

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Can we restrict a user from deleting a particualr sheet ? I thought of locking and passwords, but the workbook gets a new sheet everyday bcoz of some processing.. . So basically, the user should not be in a position to delete a particular sheet(lets call it IMPSHEET), but can do ANYTHING with the rest of the work book?

    Thank u.

  • This may not be exactly what you want, but if you go to Tools>Protection and make sure Workbook Structure is checked you will prevent a user from deleting sheets, it also stops them from adding sheets,hiding & unhiding, renaming and moving sheets.Unfortunately it applies to all sheets so Protection will have to be removed before you can add a new sheet.

  • Derk,
    Thank u for the help that u gave me previously. Now, I submitted another request on Stop Delete Sheet , but no-one could help me the way I wanted. So, Iam submitting another one...which I should not....but this is important and my project is stuck up bocz of this:

    I want to prevent users from deleting a particular sheet. Lets name it IMPSHEET. I thought of protection and locking, but every hour a new sheet gets added to the workbook(they delete it also sometimes if they dont like) and I dont want to annoy the user by asking the same password each time they add a sheet. My boss does not want a password at all. All i can think of is API now.. Can u help me with this? OR we can do one more thing...we should ask the password ONLY when he tries to delete that particular sheet...

    Thank u

  • I have merged the two threads.

    I notice that you didn't even take the trouble to tell Roy why his suggestion was not of any help.

    By posting new topics when you don't get a suitable (or any) answer to your original thread, all you do is to create more work for us admins and increase the likelihood of people trying to solve problems that have already been solved elsewhere.

    Please remember that this is a FREE resource staffed by very skilled volunteers who give up their time to help people like you for no financial gain. It is thereforein your interest not to abuse this help.



  • One thing to try is modify the main menu to hide the "Edit / Delete Sheet" item when activating IMPSHEET then putting the menu item back when leaving the sheet. This is not absolute protection - if the user has added a Delete Sheet button to one of the toolbars, it would still work (I would think that most people don't have that button added though)

    Put the following VBA Code in the IMPSHEET object (not in a general module).

    Private Sub Worksheet_Activate()
    Set mymenubar = CommandBars.ActiveMenuBar
    mymenubar.Controls("Edit").Controls("Delete sheet").Visible = False
    End Sub

    Private Sub Worksheet_Deactivate()
    Set mymenubar = CommandBars.ActiveMenuBar
    mymenubar.Controls("Edit").Controls("Delete sheet").Visible = True
    End Sub


  • In addition to ralph's good idea, you could put the following code into the Thisworkbook module. If the user does delete the sheet, then he won't be able to save the workbook.

  • Sorry I don´t understand your question.
    You want prevent to delete some definite area in IMPSHEET or prevent to delete whole sheet?

    If you walked away smiling-then for you the price was right

  • As well as disabling Delete Sheet on the Edit menu, to disable the worksheet tab pop-up menu :-

    Private Sub Worksheet_Activate()
    CommandBars("Ply").Enabled = False
    End Sub

    Private Sub Worksheet_Deactivate()
    CommandBars("Ply").Enabled = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!