Menus in Excel 2007

Geoff · Aug 17, 2006

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

Geoff · Aug 17, 2006

Just tested without On Error Resume Next and mine crashed at 'Ply' with
'Invalid proc call'

Geoff

Tom Ogilvy · Aug 17, 2006

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

Tom Ogilvy · Aug 17, 2006

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.

Geoff · Aug 17, 2006

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

Ron de Bruin · Aug 17, 2006

Ply is renamed in Beta 2 to Sheet tab

In the final release it is Ply again

Geoff · Aug 17, 2006

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

Geoff · Aug 17, 2006

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

Tom Ogilvy · Aug 17, 2006

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.

Ron de Bruin · Aug 17, 2006

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

http://blogs.msdn.com/jensenh/archive/2006/05/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

Geoff · Aug 17, 2006

<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

Jim Rech · Aug 17, 2006

Ron de Bruin · Aug 17, 2006

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

Geoff · Aug 17, 2006

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

Geoff · Aug 17, 2006

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 · Aug 17, 2006

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

http://blogs.msdn.com/jensenh/archive/2006/05/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

Geoff · Aug 17, 2006

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
Insert>WorkSheet, Edit>Delete Sheet or Edit>Move or Copy Sheet.
Yes I know where Cells>Insert Cells>Delete and Cells>Format 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 · Aug 19, 2006

Hi Geoff

Different in 2007 (xml now)

Read this

http://blogs.msdn.com/jensenh/archive/2006/05/25/606819.aspx

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

halimnurikhwan · Aug 19, 2006

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

Ron de Bruin · Aug 19, 2006

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

Working outlook via vba code with xlsx - error	0	Jul 18, 2023
Stripping down excel	2	Sep 29, 2008
auto enable macros	5	Sep 25, 2008
Custom Right Click	1	Jun 25, 2008
VBA for Dependents shortcut menu	0	May 22, 2019
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser)	0	Oct 24, 2022
Disable formula Bar in Option Menu	3	May 11, 2009
Workbook_Open() won't run macro unless breakpoint set	1	Apr 24, 2008

Menus in Excel 2007

Geoff

Geoff

Tom Ogilvy

Tom Ogilvy

Geoff

Ron de Bruin

Geoff

Geoff

Tom Ogilvy

Ron de Bruin

Geoff

Jim Rech

Ron de Bruin

Geoff

Geoff

Ron de Bruin

Geoff

Ron de Bruin

halimnurikhwan

Ron de Bruin

Ask a Question

Similar Threads