VBA to disable "Delete...." on shortcut menu of worksheet tab?

I have a spreadsheet where I don't want users to be able to delete a worksheet. How do I code it so that the "Delete..." on the shortcut menu for the worksheet tabs is disabled? I'm thinking that part of the code is:

.Controls("Delete...").enabled=False


but can't figure out the rest.

Thanks for any help!

Use this

Application.CommandBars("Ply").FindControl(ID:=847).Enabled = False

See also
http://www.rondebruin.com/menuid.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Mary Kathryn" <Mary.Kathryn.243n6m_1141398604.0775@excelforum-nospam.com> wrote in message
news:Mary.Kathryn.243n6m_1141398604.0775@excelforum-nospam.com...
>
> I have a spreadsheet where I don't want users to be able to delete a
> worksheet. How do I code it so that the "Delete..." on the shortcut
> menu for the worksheet tabs is disabled? I'm thinking that part of the
> code is:
>
> Controls("Delete...").enabled=False
>
>
> but can't figure out the rest.
>
> Thanks for any help!
>
>
> --
> Mary Kathryn
> ------------------------------------------------------------------------
> Mary Kathryn's Profile: http://www.excelforum.com/member.php...o&userid=31905
> View this thread: http://www.excelforum.com/showthread...hreadid=518725
>

That did the trick! I sure do appreciate your help!

There's an option to delete sheets under Edit (on the worksheet menubar).

And the user can just drag that tab to another workbook.

Just a warning that there are other ways to delete sheets.

You may want to consider using Tools|Protection|protect workbook and check that
Structure option. The user won't be able to move/delete/insert/rename/copy any
of those worksheets.

Mary Kathryn wrote:
>
> I have a spreadsheet where I don't want users to be able to delete a
> worksheet. How do I code it so that the "Delete..." on the shortcut
> menu for the worksheet tabs is disabled? I'm thinking that part of the
> code is:
>
> Controls("Delete...").enabled=False
>
> but can't figure out the rest.
>
> Thanks for any help!
>
> --
> Mary Kathryn
> ------------------------------------------------------------------------
> Mary Kathryn's Profile: http://www.excelforum.com/member.php...o&userid=31905
> View this thread: http://www.excelforum.com/showthread...hreadid=518725

--

Dave Peterson

I was googling to find delete sheet in vba & I found this thread a good one.