How do I remove the "Delete" option when I right click on sheet tab - HELP HELP HELP

[Barryoffshore]

When right clicking on the sheet tab, I need to know if it is possible to remove the "Delete" from being an option. My workbook is structured to where if a single sheet is deleted, it screws up all my formulas. I tried to just password protect the workbook, but doing that removes the "Rename" function, which I still need. I need someone who is way smarter than I am to please PLEASE HELP ME!!!

 

[Barryoffshore]

I really need some help here people...

 

[VBA Geek]

see if this does the job for you

Application.CommandBars(44).Controls(2).Delete Temporary:=True

 

[Barryoffshore]

It probably will work, but I don't know where to put it in at? When I put that into a blank VBA it returns an error. I'm sorry I need so much help

 

[VBA Geek]

try

Sub RemDel
      Application.CommandBars(44).Controls(2).Delete Temporary:=True

End Sub 
</pre>

 

[Barryoffshore]

well shoot... I'm still doing something wrong

I guess I need you to lead me step by step - again, I'm sorry for being so stupid

 

[Barryoffshore]

VBA Geek,

I'm sure your code works, I'm just not smart enough to know where to put it... If you can give me just a little more guidance, I would really appreciate it. You can email me at E-Mail address removed - Moderator if you'd like. Thank you again for all your help!!!

NOTE - If you need to give someone your e-mail address, you can use the secure PM option - Moderator

 

[CharlesH]

Hi,

If you password protect the "Workbook" the user can still make changes to the sheet. But they can not delete the sheets.

 

[Barryoffshore]

CharlesH,

THANK YOU for your input... I need to be able to change the names of the sheet tabs, and if I protect the workbook, that feature is then disabled. If there is a way to protect the Workbook and keep the "Rename" feature operational then that would be great!!!

 

[CharlesH]

Hi,

Tanks for letting me know. As to the answer Geek supplied.
You can place in the "This workbook" module.
I think this will disable the "Delete function when you open the work book.