BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Excel 2003 Style Menu in Excel 2007/2010

I really don't want to write this article as I'm in love with new interface of Excel 2007 and wants my blog readers and every excel user to understand the new Ribbon Menu structure than to go back and use Excel 2003 style menu in Excel 2007. However I don't want to write but I'm not writing this article on a gun-point. Lolzzzzzzzz... The objective is to help those users who want to remain stick with legacy programs just because they don't understand new and improved systems. So, that they can move to new systems, explore it, gradually learn it and hence praise it.  Second reason is that such utilities are available @ cost of $20-$50 over the internet which my dear readers and excel users do not need to pay anyway.
Excel 2003 Style Menu in Excel 2007 
 In my total experience with Excel and Excel users, I encountered many peoples (major of them Senior Managers and Managers) who resist Excel 2007 primarily due to its new ribbon structure. They were habitual to 2003 and before menu style and found it hard to locate their favorite commands. Anyway, let's come to technical part now.



Excel 2003 Menu

Excel 2007 introduced ribbon structure which is carried forward in Excel 2010 too and hence will continue to be part of Excel in future. This ribbon can be modified by any developer with the knowledge of XML and Ribbon key elements. However, Microsoft still kept the old command bars and command buttons, somewhere inside Excel 2007 and 2010 too.

Excel 2007 Ribbon Menu Structure

If you want to see the old command buttons and want to use them in Excel 2007/2010. Click on MS Office Button(2007) or File(2010) and go to Excel Options --> Customize --> 'Choose commands from' --> All Commands. Either find and select the options you want or run the macro below which will create the exact 2003 style menu in Add-ins tab of Excel 2007.



Sub Show_Excel_2003_Style_Menu()
    On Error Resume Next
    Dim cmdBar As CommandBar
    Dim cmdBarCtrl As CommandBarControl
    Dim sMenuName As String
    Dim sToolbarName As String
    Dim iMenu As Integer

    sMenuName = "Excel 2003 Style Menu"
    sToolbarName = "Excel 2003 Style Toolbar"
    CommandBars(sMenuName).Delete
    CommandBars(sToolbarName).Delete
    'Code upto here can be used to hide/delete Excel 2003
    'style menu from Excel 2007/2010.
  
    Set cmdBar = CommandBars.Add(sMenuName, , , True)

    'This will create Excel 2003 style menu bar in Excel 2007/2010
    With cmdBar
        .Visible = True
        For iMenu = 1 To 10
            Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30001 + iMenu)
        Next iMenu
        Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30022) 'Chart
        Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30177) 'AutoShapes
    End With


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




If you don't know or understand the VBA macros, download the add-in here.


Anubhav said...

I always missed 2003 style menu. This is great :)

Mark Boulton said...

I thank the author of this article for providing a "solution" to the 2007 ribbon interface mess even though it is really just a workaround.

I have used MS office applications since 1990, including the first releases of MS Word, Excel and Access on the Mac (MS Word as became WinWord was available on the Mac before it was available in Windows).

Remember that both Windows and Mac OS are both flavours of what generically are called "WIMPS" (Windows Icons Menus Pointers) interfaces, and the whole purpose and benefit of them were (a) intuitiveness, and (b) consistency.

Office 2007/2010 however kick that into touch with this ill-advised and badly-implemented re-hash at a system that already worked perfectly, from a user's and from a developer's point of view.

I have spent 3 years trying to give 2007 a fair crack of the whip, and as an MS Office/VBA developer of over 13 years, I still struggle and find it a pain. It's not the fact that it's DIFFERENT that annoys me, and it's not that it scares me. Anyone who tells me those are the reasons why people don't like the ribbon structure are patronising beyond belief. Yes, common software has had changes and updates since time immemorial, but such changes were always implemented keeping in mind the basic ethos "if it ain't broke, don't fix it". The ribbon doesn't fix anything that was broke, and it introduces so many new levels of UI control. You have this curious mixture of small icons, large icons, menus and dropdowns all coagulated together in one space, some commands which don't appear at all unless you click on an icon which, in fact, doesn't fire off a command itself, but displays a little coloured segment of the app's title bar (such as "Picture Tools") which must be clicked in order to 'unhide' the portions of the Ribbon that need to be navigated to find things that easily and intuitively just appeared as a floating command bar near the mouse pointer. Now, you have to go to some part of the Ribbon (can't remember which), click on one of the icons to bring up "Picture Tools", click on that, click on another icon to bring up a very long icon-drenched menu, go right to the bottom, and click on the bottom choice to bring up another level of icons, and then you click that.

Mark Boulton said...

(continued:) Not only is it non-user-friendly, the Ribbon structure doesn't even sit within the VBA Object Model for the Application. Whyever not? More proof, if any more were needed, that it was shoe-horned in just to satisfy the Google-esque "open-standards"-indoctrinated developer community, who want to do everything via XML. Why? Why is it that every other part of MS Office apps can be controlled at design-time and run-time via the VBA Object Model, but the Ribbon cannot? VBA only has one method relating to the Rubbin - UIVisible. You can set it to True or False. Nothing else. You can't add or remove controls. You can't, for example, hide/grey out controls that are 'out of context' according to conditions in your macros - you can't modify that all-important XML at run-time - you have to unzip the XLSM file, hand-code the changes then re-zip the file. You can't make it react to any events or other routines. How can you create a proper, robust interface like that? People with memories slightly longer than 3 years will recall that before MS Word became popular, the best most people had for creating documents on a computer was WordPerfect for DOS. In that, you couldn't edit a document in "WYSIWYG" format (What You See Is What You Get", you only saw it in plain text. To specify any formatting, you had to insert codes, which were tags similar to what HTML and XML give you. For a good 15 years that approach was seen as having gone out with the ark. All of a sudden now for some reason though, it's back in vogue, and again touted as a "new and improved" system - how the heck is hand-coding text tags better than WYSIWYG, drag-and-drop operation? How is it more intuitive, "discoverable" and "extensible"? I fail to see it myself, and that's as a developer.

The nomenclature of the Ribbon is confusing in itself - why is what used to be called the "File" menu just an unlabelled lapel badge? What is meant by "Home"? Different users would have different ideas of what you'd find in a "Home" menu - some would expect it to be the "File" menu, some would expect it to be the "Format" menu. Of course, it's the latter, but why not then call the tab "Format"?

Obviously, MS know that users might want to customize the ribbon, but the only option available is the ability to move the old-style "command bar" icons to the top-left-hand corner of the app's title bar - you can't, like in 2003 and before, just drag-and-drop any icon into any command bar (or any Ribbon tab). Again, why not? MS used to believe in user-customizability, now they want to restrict. Again, how is this a "new and improved system"?

Plus, don't get me started on how 2007/2010 have so many quirks and just out-and-out bugs when it comes to screen rendering - not only is it just slower overall (about 1/6th of the speed of 2003, and 2003 was already about half speed of 2000 and prior) but when you use the zoom slider it zooms the cells but not Autoshapes, unless you do Print Preview to 'force redraw' it. I could go on and on, but believe me, I have supported dozens of people in various companies, and ALL of them say they hate the 2007 interface, even if they've been forced to try and "get used to it" for 3 years. Their opinion doesn't change, but they just end up taking the view "it's never going to go away, so we just have to grin and bear it". But whenever they get to use any of our 2003-installed computers again, they always comment on how much better that version was, and they wish they could get it back.

Ashish Jain said...

Well that was a big comment. I appreciate the time taken for writing this beautiful appreciation.

my readers like what I post is my reward. Receiving such a beautiful comment on first day of the year - made my day and year :)

Thanks mr. Mark Boulton.

Anonymous said...

I class myself as an Excel expert but having just made the jump from Office 2003 to 2010 I and the rest of my collegues at work cannot believe what a mess Microsoft has made of it. yes, they've added new features, but the interface has a lot to be desired.

Take Inserting columns. You would think it would fall under the 'Insert' tab, but no. It's under the 'home'tab instead. Logical? No.

i could go on and on, but why bother. MS decreased my productivty as I constantly try and remember where they have placed controls, and some don't seem to be accessible via the interface anymore.

Goto specials was always a handy little dialog box, but the only way I can access it is via a Ctrl+G shortcut.

I don't like it one little bit - and I don't think I ever will. I may get used to it, but nothing more.

Ashish Jain said...

The only constant in Life is change.... and so with softwares being part of life :)

Shortcuts of Office 2003 still works in Office 2007 and 2010. You can still open "GoTo Special" via Ctrl+G in office 2007/2010.

I hope the Excel 2003 style menu in Excel 2007 helped you a lot with your favorite menu commands at the right place like before.

Since, you're an excel expert, I can understand that for long you were using Excel 2003. But try to give Excel 2007/2010, some more time and it will help you gain productivity at a much faster pace.

Happy to help you, whenever needed :)

Regards
Ashish Jain
+91-9999-40-48-43

Muneeb said...

Wonderful. I really like it.

Bob said...

I am expert in Excel/VBA since '93, i am working with 2007 for 3 years now. I still have my laptop with Excel2003 if am in hurry under pressure to fix something.

I read a lot of forums and lots and lots of people completely HATE DEEPLY the 2007-2010 ribbon of MS. Even when you get used to it like i do i dont understand why they created it. Nowadays i hear people even switch to OpenOffice just because of the terrible Ribbon of MS.

Ms should do something about the bs ribbon, example create a standard switch to a 2003 menu + the extra features of 2007-2010, that would do the trick for millions of people, especially in finance.

Mark Boulton said...

I discovered something very useful in Word 2007 the other day; don't know if it's the case in Excel (I don't have 2007 at home). You can use any of the old ALT shortcuts from 2003 and earlier, and as you begin to type them, a little hover-box (like a ToolTip) appears above the ribbon showing you're typing a 2003 shortcut and even indicates what command it thinks you're performing (which amazing upon amazing, in always got right).

For instance, I was showing someone how I used to manipulate tables, for instance, how to just completely delete whatever table the cursor is sitting on. I was about to demonstrate how doing it the 2003 way wouldn't work in 2007, not being sure how to do it via the Ribbon, and was amazed when ALT, A, D, T did the job (A=tAble menu, D=Delete, T=Table).

I don't understand how the Ribbon gives you more productivity at a faster pace though. It slows down novices and experts alike. I don't understand why those trying to "sell" us the concept keep telling us we just have to work harder to appreciate it, so we can praise it. Can't you understand that you can't *force* people to praise something. People will only praise something they like. You can't force people to like something, when they quite obviously don't.

Chris said...

What people seem to be missing is that Microsoft has to keep selling stuff to stay in business.
They ran out of ideas for new feature (that you don't need) so hit on the idea of a new interface aka the ribbon.
Then they removed some functionality and sold it as Office 2007. That left them the option of adding back the 2000/2003 functionality and selling it, again, as Office 2010.
Personally, I think some of the bad features of 2007 have actually got worse woth 2010!

Stuart said...

Thanks for this code Ashish.

Can I ask where you found the ID codes for each of the Excel 2003 menu buttons?

Also, do you know the ID code for the Style Dropdown box? This is not on the default Excel 2003 menu, but can be added in Excel 2003. I can't find it in the Excel 2007 command list so want to add it by VBA code.

It is a dropdown box that shows the current Style of the selected cell, and selection via the dropdown list of whichever style you want to choose.

Many thanks,

Stuart

www.giaxaydung.vn said...

I want Add-in menu active each time run this code. What can I do?

evelynm said...

Actually I also suffer from the ribbon interface, which divides the functions into different groups and is hard to find out the familiar tools.

I have found an Office add-in Classic Menu for Office 2007 and 2010. What a wonderful app is. It brings back the old 2003 drop down menu into the new ribbon. Both new functions and old menus can be at your hand.



Actually I also suffer from the ribbon interface, which divides the functions into different groups and is hard to find out the familiar tools.

I have found an Office add-in Classic Menu for Office 2007 and 2010. What a wonderful app is. It brings back the old 2003 drop down menu into the new ribbon. Both new functions and old menus can be at your hand.

Classic Menu for Office 2007 and 2010 devote several years in researching the menu, and considered as the most stable add-ins of classic menu field. It contains 2007 and 2010 suite, which fits for different Office user. Classic Menu of http://www.addintools.com includes all: Word, Excel, PowerPoint, OneNote, Outlook, Publisher, Access, InfoPath, Visio, Project.


I have found an Office add-in Classic Menu for Office 2007 and 2010. What a wonderful app is. It brings back the old 2003 drop down menu into the new ribbon. Both new functions and old menus can be at your hand.You can save your time and money on the training.

The classic view allows you to work with Office 2007 as if it were Office 2003. Not only includes Classic Menu for Word, Excel,PowerPoint 2007.

Heli said...

tried to run under module and worksheet - nothing. Found from Options All commands, but don't understand how to make it work so it would work automatically ...

Heli said...

tried to run under module and worksheet - nothing. Found from Options All commands, but don't understand how to make it work so it would work automatically ...

Subu said...

Hi


thanks for this help

I'm using XL 2010 on a win 7 machine

I downloaded the *.XLAm file from your link

opened XL 2010, went to developer, add in, included your vba

saved that (blank) file with a name

Still nothing changed ..

what am I missing


regards
subu

Anonymous said...

I just want to say that I too am a long time user of Office 2003 and before. I started using Access when it first came out. I agree whole heartedly with Mark Boulton said.