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]

CharlesH,

I tried doing that just now and it didn't work... I know I must be doing something wrong because it should work. Thank you for trying - I think I'll go pull my hair out now!!! LoL

 

[CharlesH]

Sorry try this code.

Private Sub Workbook_Open()
Application.CommandBars("Ply").Controls("Delete").Enabled = False
End Sub

I tested it and it worked for me.

 

[Barryoffshore]

CharlesH,

Again, thank you for trying so hard... I must be the dumbest guy on this forum because I can't make it work. I open my workbook, go to the Developer tab at the top, then to Visual Basic, then duoble click on the "ThisWorkbook" and I paste what you sent onto the blank sheet. I save and close Visual Basic, then save and close the workbook. But when I reopen the workbook and see if I can delete a sheet tab, I still can... Like I say, I must be doing something wrong.

Bear

 

[CharlesH]

What is your version of Excel. This work in 2007.

 

[Barryoffshore]

it's Microsoft 2010

Bear

 

[CharlesH]

Just tested in 2010. Does not work for me either.

 

[Barryoffshore]

Well that makes me feel better - at least it means that maybe I'm not a complete idiot! LoL!!!

 

[jasonb75]

See if this helps

How to disable Right Click Delete Sheet Command in Excel 2010 using VBA

 

[Barryoffshore]

jasonb75,

AWESOME!!! That worked exactly as I needed!!!

CharlesH - You gotta try this!!!

I thank both of you VERY VERY MUCH!!!

Bear

 

[CharlesH]

Thanks I'll take a look.