Hi Geoff

Different in 2007 (xml now)

Read this

http://blogs.msdn.com/jensenh/archiv...25/606819.aspx

Start here
http://msdn2.microsoft.com/en-us/library/ms406046.aspx


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



"Geoff" wrote in message ...
I understand what you are saying about using ids. However i do not know the
idsin 2007. Regarding using menu names, when I ran Tom's piece of code to
show the menus in 2007 it was easy to see that Ply had changed to Sheet tab
becuase of the associated submenus. I cannot apply the same logic to finding
InsertWorkSheet, EditDelete Sheet or EditMove or Copy Sheet.
Yes I know where CellsInsert CellsDelete and CellsFormat are in 2007 but
i cannot determine their ids or their main menu title - as you are aware, it
is certainly not 'Home'.

Geoff

"Ron de Bruin" wrote:

In 2007 you must build and change your menu's different
http://pschmid.net/

http://blogs.msdn.com/jensenh/archiv...25/606819.aspx

Not so easy


If I add the old menu's in the ribbon with the first macro and run test it is working for me

Sub CreateExcel11Menus()

With Application.CommandBars.Add("Excel 11", , True, True)
.Controls.Add ID:=30003 'Edit
.Visible = True
End With

End Sub

Sub test()
With Application.CommandBars("Excel 11")
.FindControl(ID:=847, Recursive:=True).Enabled = False
.FindControl(ID:=848, Recursive:=True).Enabled = False
End With
End Sub



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



"Geoff" wrote in message ...
Ron

Using
With Application.CommandBars("Worksheet Menu Bar")
.FindControl(ID:=847, Recursive:=True).Enabled = False
.FindControl(ID:=848, Recursive:=True).Enabled = False
End With

the ids 847 and 848 do not disable the menus in 2007. They run without a
runtime error but do not execute as expected.

They will in 2003 of course, but not in 2007.

Geoff

"Ron de Bruin" wrote:

Hi Jim

Not in a Dutch or other non English version

We can not use the English names for the controls on a menubar

Use the ID

With Application.CommandBars("Worksheet Menu Bar")
.FindControl(ID:=847, Recursive:=True).Enabled = False
.FindControl(ID:=848, Recursive:=True).Enabled = False
End With


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



"Jim Rech" wrote in message ...
With Application.CommandBars("Edit")
This is wrong. Edit is a part of the Worksheet Menu Bar


But it does work, Ron, at least with English Excel.


MsgBox CommandBars("Edit").Controls(1).Caption

--
Jim
"Ron de Bruin" wrote in message
...
| In 2007 you must build your menu's different
| http://pschmid.net/
|
| http://blogs.msdn.com/jensenh/archiv...25/606819.aspx
|
|
| You can add the old menu's in the ribbon on the Add-in tab but that's not
the way to go
|
| With Application.CommandBars("Edit")
| This is wrong
|
| Edit is a part of the Worksheet Menu Bar
|
| See
| http://www.rondebruin.nl/menuid.htm
|
|
| --
| Regards Ron de Bruin
| http://www.rondebruin.nl
|
|
|
| "Geoff" wrote in message
...
| Hi Ron
| Our posts have crossed. I was able to discover the change from Tom's
| output. Thank you for the added info about returning to Ply in the
fure.
| But my other menus are still unusable, I assume because I refer to the
old
| style headers. How can I make these effective in Beta 2?
|
| Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=852,
| Recursive:=True).Enabled = False
|
| With Application.CommandBars("Edit")
| .Controls("Delete Sheet").Enabled = False
| .Controls("Move or Copy Sheet...").Enabled = False
| End With
|
| Geoff
|
| "Geoff" wrote:
|
| That is very interesting. From the code output it appears the menu
title
| 'Ply' as applies to sheet tabs has been changed to 'Sheet tab' (as per
row 43)
|
| I amended my original code from 'Ply' to 'Sheet tab' and it now works
as
| required, including the ne winsert button on the sheet tab bar.
|
| At least this takes me half way.
|
| Thank you.
|
| Geoff
|
| "Tom Ogilvy" wrote:
|
| I didn't get any error. However, the PLY menu is when you right
click on a
| sheet tab.
|
| the "CELL" Menu/Commandbar is for the right click on a cell. (which
might
| explain your trouble with PLY).
|
| There are actually two menus named cell. One is for the right click
in
| normal view and one is for the right click in Pagebreak Preview mode.
I
| believe if you just say
| Commandbars("Cell")
| you get the normal view menu.
|
| run this with a blank sheet as the activesheet
|
| Sub listmenus()
| Cells(1,1).Value = "Header"
| cells(1,1).Font.bold = True
| rw = 2
| For Each cb In Application.CommandBars
| Cells(rw, 1).Value = cb.Name
| icol = 3
| For Each cb1 In cb.Controls
| Cells(rw, icol).Value = cb1.Caption
| icol = icol + 1
| Next
| rw = rw + 1
| Next
| End Sub
|
| --
| Regards,
| Tom Ogilvy
|
| "Geoff" wrote:
|
| Just tested without On Error Resume Next and mine crashed at 'Ply'
with
| 'Invalid proc call'
|
| Geoff
|
| "Geoff" wrote:
|
| That's odd - I wonder how the wbooks may differ. I run this as
part of an
| add-in where I discourage people trying to meddle by inserting or
deleting
| sheets. In particular, with
Application.CommandBars("Ply").Enabled = False I
| am, as you can tell, trying to disable right clicking on the
sheet tabs.
| This used to work ok but now in 2007 i can right click away
without
| restriction and i wondered if maybe menu titles had been changed.
|
| Geoff
|
| "Tom Ogilvy" wrote:
|
| I commented out On Error Resume Next and ran it in Excel 2007.
It ran fine
| for me and took all the programmed actions. Then I changed all
the False's
| to True and restored everything. So I don't think the problem
is
| necessarily xl2007 itself. Are you using smart menus? I
don't, but if you
| do, they might be problematic if the items are not visible - I
can't say for
| sure because I haven't tested it.
|
| --
| Regards,
| Tom Ogilvy
|
|
| "Geoff" wrote:
|
| Not sure if this is the correct group but...
| This proc in E 2003 no longer does what is required in E 2007
and only gets
| by because of 'On Error Resume Next':
|
| Sub DisableMenus()
|
| '''Disable 'Sheet' menus
| On Error Resume Next
|
| '''Insert Worksheet
| Application.CommandBars("Worksheet Menu
Bar").FindControl(ID:=852,
| Recursive:=True).Enabled = False
|
| With Application.CommandBars("Edit")
| .Controls("Delete Sheet").Enabled = False
| .Controls("Move or Copy Sheet...").Enabled = False
| End With
|
| Application.CommandBars("Ply").Enabled = False
|
| On Error GoTo 0
|
| End Sub
|
| Does anyone have an idea how to convert?
|
| Geoff
|
|