I have some code that works absolutely fines in Excel 97
but is giving "odd" behaviour in XP.

It's to do with the right click menu on the worksheet
tabs - I'm trying to selectively disable certain aspects
(e.g. view code).

What used to work is:
Application.CommandBars(27).Controls("view code").Enabled
= False

I've tried to work out how to get this menu control using:
For Each ctl In Application.CommandBars("workbook
tabs").Controls
Debug.Print ctl.Caption
Next

But this returns the names of each of the worksheets.
If I do something like:
Application.CommandBars("workbook tabs").ShowPopup

I get a menu with the three worksheet names as a menu.

Hmm Very confused. Anyone know how I can set these
controls to be enabled?

Thanks

Leyton

ps I'd like to keep this working in both Excel 97 and
2002!

Hi Leyton

27 is a index number and not the number of the Ply bar

Application.CommandBars("Ply").Controls("view code").Enabled = False

Or better use this (work also in a Dutch version for example)

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




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


"Leyton" wrote in message ...
I have some code that works absolutely fines in Excel 97
but is giving "odd" behaviour in XP.

It's to do with the right click menu on the worksheet
tabs - I'm trying to selectively disable certain aspects
(e.g. view code).

What used to work is:
Application.CommandBars(27).Controls("view code").Enabled
= False

I've tried to work out how to get this menu control using:
For Each ctl In Application.CommandBars("workbook
tabs").Controls
Debug.Print ctl.Caption
Next

But this returns the names of each of the worksheets.
If I do something like:
Application.CommandBars("workbook tabs").ShowPopup

I get a menu with the three worksheet names as a menu.

Hmm Very confused. Anyone know how I can set these
controls to be enabled?

Thanks

Leyton

ps I'd like to keep this working in both Excel 97 and
2002!

Thanks Ron,

One further question though is has the index changed
since '97. the reason I ask is that now that you mention
it I did (once) know that this is the PLY bar (it was a
while ago I coded this originally!!) but I kept having
trouble referring to it directly hence using the index.

Cheers

Leyton

-----Original Message-----
Hi Leyton

27 is a index number and not the number of the Ply bar

Application.CommandBars("Ply").Controls("view
code").Enabled = False

Or better use this (work also in a Dutch version for
example)

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




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


"Leyton" wrote in
message ...
I have some code that works absolutely fines in Excel
97
but is giving "odd" behaviour in XP.

It's to do with the right click menu on the worksheet
tabs - I'm trying to selectively disable certain
aspects
(e.g. view code).

What used to work is:
Application.CommandBars(27).Controls("view
code").Enabled
= False

I've tried to work out how to get this menu control
using:
For Each ctl In Application.CommandBars("workbook
tabs").Controls
Debug.Print ctl.Caption
Next

But this returns the names of each of the worksheets.
If I do something like:
Application.CommandBars("workbook tabs").ShowPopup

I get a menu with the three worksheet names as a menu.

Hmm Very confused. Anyone know how I can set these
controls to be enabled?

Thanks

Leyton

ps I'd like to keep this working in both Excel 97 and
2002!


.

Hi Leyton

One further question though is has the index changed
Yes

Never us the Index because a user can add a commandbar also.
If you run this on a empty sheet you get a list

Sub Get_Commandbars_Info()
Dim CBar As CommandBar
Dim NewWS As Worksheet
Dim RNum As Long

RNum = 1
Set NewWS = Worksheets.Add
For Each CBar In Application.CommandBars
NewWS.Cells(RNum, "A").Value = CBar.Name
NewWS.Cells(RNum, "B").Value = CBar.NameLocal
NewWS.Cells(RNum, "C").Value = CBar.Index
RNum = RNum + 1
Next CBar
NewWS.Columns.AutoFit
End Sub

Use always the English name of the commandbar and use findcontrol or the local name for the
controls.

More info you can find here
http://www.rondebruin.com/menuid.htm



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


"Leyton" wrote in message ...
Thanks Ron,

One further question though is has the index changed
since '97. the reason I ask is that now that you mention
it I did (once) know that this is the PLY bar (it was a
while ago I coded this originally!!) but I kept having
trouble referring to it directly hence using the index.

Cheers

Leyton

-----Original Message-----
Hi Leyton

27 is a index number and not the number of the Ply bar

Application.CommandBars("Ply").Controls("view
code").Enabled = False

Or better use this (work also in a Dutch version for
example)

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




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


"Leyton" wrote in
message ...
I have some code that works absolutely fines in Excel
97
but is giving "odd" behaviour in XP.

It's to do with the right click menu on the worksheet
tabs - I'm trying to selectively disable certain
aspects
(e.g. view code).

What used to work is:
Application.CommandBars(27).Controls("view
code").Enabled
= False

I've tried to work out how to get this menu control
using:
For Each ctl In Application.CommandBars("workbook
tabs").Controls
Debug.Print ctl.Caption
Next

But this returns the names of each of the worksheets.
If I do something like:
Application.CommandBars("workbook tabs").ShowPopup

I get a menu with the three worksheet names as a menu.

Hmm Very confused. Anyone know how I can set these
controls to be enabled?

Thanks

Leyton

ps I'd like to keep this working in both Excel 97 and
2002!


.