On Apr 17, 6:59 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Make it sheet activate and deactivate event code to disable for that sheet then
enable when you switch to other sheet.

Here is a list of the Ply Control ID's

Parent: Control Name: ID#:
Ply &Ungroup Sheets 1968
Ply &Insert... 945
Ply &Delete 847
Ply &Rename 889
Ply &Move or Copy... 848
Ply &Select All Sheets 946
Ply &Tab Color... 5747
Ply &View Code 1561

Private Sub Worksheet_Activate()
Application.CommandBars("Ply").FindControl(ID:=889 ).Enabled = False
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Ply").FindControl(ID:=889 ).Enabled = True
End Sub

For much more on Commandbars........ID's etc. down;oad Ole Erlandson's
Commandbars Tools.

http://www.erlandsendata.no/english/...oadcommandbars

Gord

On Thu, 17 Apr 2008 09:09:55 -0700 wrote:
On Apr 17, 11:56 am, Gord Dibben <gorddibbATshawDOTca wrote:
You could remove the "rename" from the right-click menu using VBA but what about
the double-click to rename?

Maybe easier to go to ToolsProtection and Protect the workbook?

Gord Dibben MS Excel MVP

On Thu, 17 Apr 2008 07:02:36 -0700 wrote:
Hi,

Please help me on this question.

When I right click on the sheet tab, I can rename the sheet name.
How do I disable function by using VBA?

Thanks a lot.

Thanks for your help.
The right click menu seems apply only within the sheet, but does not
apply when I right click on the sheet tab, try to rename it.
I just want to disable on one sheet but not others so I cannot use the
protection on the workbook.

I got something from other link :
Application.CommandBars("Ply").FindControl(ID:=84 7 ).Enabled = False

How do I find out which control correspond to which ID, like 847 is
for insert etc.

Other suggestions, thanks.

Hi Gord,

Thanks a lot for your help.