How to disable "Delete" worksheet

[Villalobos]

Hello,

I would like to avoid that the user can delete a specified worksheet. Could somebody help me in that how is it possible to disable the "Delete" function? The worksheet should be remain editable.

Thanks in advance the reply!

 

[NARAYANK991]

Hi ,

Can you check out this link , there are a couple of suggestions :

http://www.mrexcel.com/forum/excel-questions/18552-how-prevent-worksheet-being-deleted.html

Narayan

 

[Villalobos]

Hello Narayan,

Thank you the link. Unfortunately I would have a problem, I followed the instructions but if I right click on Worksheet tab and select the Delete then the function remains enabled. May be I doing something wrong?

I have place the code in sheets module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CommBarTmp, Commbar As CommandBar
For Each Commbar In Application.CommandBars
  Set CommBarTmp = Commbar.FindControl(ID:=847, recursive:=True)
  If Not CommBarTmp Is Nothing Then CommBarTmp.Enabled = True
Next
End Sub

 

[NARAYANK991]

Hi ,

I think it should be :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim CommBarTmp As CommandBarControl, Commbar As CommandBar

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

Narayan

 

[Villalobos]

Hi,

I have tried this but the sheet delete option still open.