Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

disable tab function on sheet tab

561 views
Skip to first unread message

johnny

unread,
Apr 17, 2008, 10:02:36 AM4/17/08
to
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

unread,
Apr 17, 2008, 11:56:04 AM4/17/08
to
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

unread,
Apr 17, 2008, 12:09:55 PM4/17/08
to
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 Tools>Protection and Protect the workbook?
>
> Gord Dibben MS Excel MVP
>
> On Thu, 17 Apr 2008 07:02:36 -0700 (PDT),johnny<leong.joh...@gmail.com> 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.


Gord Dibben

unread,
Apr 17, 2008, 6:59:59 PM4/17/08
to
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

unread,
Apr 19, 2008, 11:27:03 AM4/19/08
to
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/index.php?d=endownloadcommandbars
>
> Gord
>
> On Thu, 17 Apr 2008 09:09:55 -0700 (PDT),johnny<leong.joh...@gmail.com> 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 Tools>Protection and Protect the workbook?
>
> >> Gord Dibben MS Excel MVP
>
> >> On Thu, 17 Apr 2008 07:02:36 -0700 (PDT),johnny<leong.joh...@gmail.com> 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.

Hi Gord,

Thanks a lot for your help.

0 new messages