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.

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 (PDT), johnny 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.

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

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

Other suggestions, thanks.

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 (PDT), johnny 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.

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.