7 thoughts on “Excel 2007 Menu

  1. Speaking of Walkenbach’s menu code, I’m using his Excel 2003 Power Programming menu examples to add back Excel 2003 menus in Excel 2007.

    I have the code running in 2003 to write out a complete list of menu, sub menu, and sub menu item names and IDs. The same names and IDs exist in Excel 2007 because I can ran the same code in 2007, but the code to add menus has no effect. What’s changed?

    For instance, the follow code should add a File menu before the Edit the menu. By the way, the control IDs are exactly the same in 2003 and 2007, but I FindControl(ID:=30002) results in a run-time error.

    Sub Add2003Menus()
    Dim fileIndex As Integer
    Dim newFileMenu As CommandBarPopup

    ‘ get the index of the the edit menu
    fileIndex = CommandBars(1).Controls(“Edit”).Index

    ‘ find the edit menu by ID
    ‘ fileIndex = CommandBars(1).FindControl(ID:=30002)

    ‘ check that fileIndex is valid
    Cells(1, 1) = fileIndex

    ‘ insert the file menu ahead of the edit menu
    Set newFileMenu = CommandBars(1).Controls.Add( _
    Type:=msoControlPopup, _
    Before:=fileIndex, _

    ‘ add a caption
    newFileMenu.Caption = “&File”

  2. Never mind. I found out the problem. All the 2003 menus get added to the Add Ins menu in the 2007 menu bar. So if I continue to add 2003 standard menu items, they will all get added in the Add Ins menu. de Bruin has a fix for moving the 2003 menus out of the ghetto Add Ins menu. Yipee!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.