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

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

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

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

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

Ply is renamed in Beta 2 to Sheet tab

In the final release it is Ply again

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



"Tom Ogilvy" wrote in message ...
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

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

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

My apologies for my inattention Geoff. I looked at xl2007 and read xl2003.
I am working in xl2003 and didn't pick up the disconnect (I even said xl2007
- whoops). Sorry for the misinformation.

Guess you can disregard what I have said.

--
Regards,
Tom Ogilvy



"Geoff" wrote:

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

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

<Guess you can disregard what I have said
On the contrary your piece of code enabled me to discover the change of name
fom Ply to Sheet tab.

Ron has provide some links for me to follow on the rest of the procedure.

Thanks to you both.

Geoff

"Tom Ogilvy" wrote:

My apologies for my inattention Geoff. I looked at xl2007 and read xl2003.
I am working in xl2003 and didn't pick up the disconnect (I even said xl2007
- whoops). Sorry for the misinformation.

Guess you can disregard what I have said.

--
Regards,
Tom Ogilvy



"Geoff" wrote:

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

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
|
|

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
|
|

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
|
|

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
|
|

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
|
|

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
|
|

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
|
|

Hi guys,

I'm working in xl2003 now, but I have a question:
Can I upgrade my xl2003 to xl2007 & how ?
I'm in Indonesia now, I think it will be different way about how to
upgrade between Asia and other place?!

Thanks,

Halim

Can I upgrade my xl2003 to xl2007 & how ?
Do you want to upgrade to beta 2 ???

You can open 2003 files in 2007 in Compatibility Mode
You can save it then as 2007 if you want.

There is no beta for the Dutch version on this moment
I am sure we (I am Dutch) have more problems than our English friends here

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



wrote in message oups.com...
Hi guys,

I'm working in xl2003 now, but I have a question:
Can I upgrade my xl2003 to xl2007 & how ?
I'm in Indonesia now, I think it will be different way about how to
upgrade between Asia and other place?!

Thanks,

Halim

Hi Geoff,

I gave this a try with the most recent internal builds of Excel and it works
fine, even without the On Error Resume Next. Of course, with Excel 2007,
there are a few new entry points to these commands on the ribbon, etc. that
are not disabled by your code but the entry points that I'd expect to be
affected by your code were.

If you're still seeing any issues, I'd ask you to try this in B2TR and let
me know (directly via email) if there are problems.

Cheers,
Dan
Excel Team

Note: We've only got a couple weeks to get bugs filed and fixed, so if I've
requested additional info via email, it'd be great if you can get that to us
ASAP. Please include any necessary sample files, as well as detailed repro
steps so that we can try to reproduce the problem on our side. Also - if
you're emailing me directly (definitely the most efficient at this point)
you'll want to fixup my email address to remove everything after danbatt and
before the @.




"Geoff" wrote in message
...
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