Hi Jim
What seems odd to me is that when following Tom's line of commenting out 'On
Error Resume next' to see where a runtime error occurred, that only happened
with 'Ply'. The other code ran but did not give the desired results. Now by
changing 'Ply' to 'Sheet tab' that part performs correctly however as of yet
I cannot get the other to perform.
All ok in 2003 but only partially in 2007.
Geoff
"Jim Rech" wrote:
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
|
|