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.
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 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.
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.
 |
| Excel 2003 Style Menu in Excel 2007 |
 |
| 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.
BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010
Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
Comments
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.
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.
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.
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.
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
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.
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.
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!
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
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.
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