disable tab function on sheet tab

[johnny]

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.

 

[Gord Dibben]

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 Tools>Protection and Protect the workbook?


Gord Dibben MS Excel MVP

 

[johnny]

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 Tools>Protection and Protect the workbook?

Gord Dibben MS Excel MVP

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:=847 ).Enabled = False

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

Other suggestions, thanks.

 

[Gord Dibben]

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/index.php?d=endownloadcommandbars


Gord

 

[johnny]

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/index.php?d=endownloadcommandbars

Gord

Hi Gord,

Thanks a lot for your help.