October 25, 2008

Bring Back Those Old Style Menus

When Excel 2007 came out, the old style menus were replaced with the Ribbon.

Actually no, this isn't what really happened.The right click menus are still there (always have been) and the menus and toobars that were formerly at the top were just hidden. But they are still there, and you can unhide them quite easily. (Note that a few menu items such as File Search are disabled for various reasons, although most of them will still work fine) This code will show the top menu and selected items from the Standard and Formatting toolbars on the Add-Ins tab.

Private Sub ShowOldStyleMenus()
    On Error Resume Next
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    Dim sMenuName As String
    Dim sToolbarName As String
    Dim iMenu As Integer

    sMenuName = "Old Style Menu"
    sToolbarName = "Old StyleToolbar"


    Set cBar = CommandBars.Add(sMenuName, , , True)

    With cBar

        .Visible = True

        For iMenu = 1 To 10

            Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30001 + iMenu)

        Next iMenu

        Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30022) 'Chart
        Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30177) 'AutoShapes

    End With


    Set cBar = CommandBars.Add(sToolbarName, , , True)

    With cBar

        .Visible = True

        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2520) 'New
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=23) 'Open
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3) 'Save
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=4) 'Print
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=109) 'Print Preview
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2) 'Spelling
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=21) 'Cut
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=19) 'Copy
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=22) 'Paste
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=108) 'Format Painter
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=210) 'Sort Ascending
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=211) 'Sort Descending
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=984) 'Help
        Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1728) 'Font
        Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1731) 'Font Size
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=113) 'Bold
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=114) 'Italic
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=115) 'Underline
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=120) 'Align Left
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=122) 'Center
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=121) 'Align Right
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=402) 'Merge and Center
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=395) 'Accounting Number Format
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=396) 'Percent Style
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=397) 'Comma Style
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=398) 'Increase Decimal
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=399) 'Decrease Decimal
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3162) 'Decrease Indent
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3161) 'Increase Indent

    End With

    Set cBar = Nothing
    Set cBarCtrl = Nothing

    On Error GoTo 0
End Sub

Here's how it looks.

Keep in mind it's also possible to do this with XML and do it better, I've seen it done with both freeware and shareware downloads (very impressive) and have also experimented with it myself. But the advantage of doing it with VBA like above is that you can just copy and paste it somewhere like your Personal workbook, or you can email it to someone in a hurry. That said, if you do have 2007, you will probably be better off learning where everything is in the long run :-)

I've added the above code to the 2007 version of JMT Excel Utilities (Show Old Style Menus in the the Developer Tools tab). The download link is here.  

Posted by andrewe at 11:19