office 2010 - how to get classic menu 12

[Nifrabar]

Hi!
I think that it will be possible using VBA to have an auto running macro which brings the classic menu.
Is there some sample code for that available?
-Bart

 

[AnotherHiggins]

Classic menu meaning the old File, Edit, View, etc instead of the Ribbon?

No, sorry.

But have a look at these interactive guides where you do something in 2003 and it shows you where that command/function is in 2010.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Is this any use:

http://spreadsheetpage.com/index.php/tip/old_style_menus_in_excel_2007/

?

I haven't tried it, as all the old keyboard shortcuts to the old menu commands work anyway ( like Alt-e-e for Edit/Replace ).



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Nifrabar]

Glenn,

Presume I can cut and pate the code as macro,
This looks fine but will it be language dependent?
I am using Dutch version and there it throws error

.Controls("&File").Copy OldMenu

throws an error but I expect also code further down will throw errors.
Any idea?

-Bart

 

[GlennUK]

Nifrabar,

I would expect that it would other errors too, and I don't know if the solution will work in a different language setting either.

I recommend using the interactive guides as suggested by John ( anotherhiggins ), to help you learn the new locations of all the commands. And using the old shortcut-key combinations, as I suggested ( and I use these all the time ). And putting anything that you use a lot into the Quick Access Toolbar ( especially useful for accessing commands that no longer exist in the Ribbon ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[strongm]

>Any idea?

Use the Menu IDs instead; they are not dependant on the language.

 

[Nifrabar]

Strongm,
Any help how to do so?

Glenn,
Just like all those other users who hate the ribbon I am looking for a 'classic' alternative. Hopefully once office will be again as users want it to be, with a possibility to toggle to the menus which have proven to be very usefull since windows v2.0 and also with other OS'
-Bart

 

[strongm]

[blue]Option Explicit

Public Sub MakeOldMenus()
    Dim OldMenu As CommandBar
    Dim OldMenu2 As CommandBar
    Dim lp As Long

    ' Delete our current custom menus, if they exist
    KillMenus

    ' Create an old-style toolbar
    ' Set the last argument to False for a (slightly) more compact menu
    Set OldMenu = Application.CommandBars.Add("Old Menus", , True)
    Set OldMenu2 = Application.CommandBars.Add("Old Menus2", , False)

    ' Now copy the controls from Excel's "Built-in Menus" shortcut menu
    ' using IDs to avoid language issues
    With Application.CommandBars("Built-in Menus")
        .FindControl(, 30002).Copy OldMenu
        .FindControl(, 30003).Copy OldMenu
        .FindControl(, 30004).Copy OldMenu
        .FindControl(, 30005).Copy OldMenu
        .FindControl(, 30006).Copy OldMenu
        .FindControl(, 30007).Copy OldMenu
        '.FindControl(, 30008).Copy OldMenu 'Does not exist in Excel
        .FindControl(, 30011).Copy OldMenu
        .FindControl(, 30009).Copy OldMenu
        .FindControl(, 30010).Copy OldMenu
    End With
     
    ' For additional fun, add Standard and Formatting toolbars below menu
    ' Unfortunately we cannot display them in the ribbon in quite the same way as we might want
    For lp = 1 To Application.CommandBars("Standard").Controls.Count
        Application.CommandBars("Standard").Controls.Item(lp).Copy OldMenu2
    Next

    For lp = 1 To Application.CommandBars("Formatting").Controls.Count
        Application.CommandBars("Formatting").Controls.Item(lp).Copy OldMenu2
    Next

    ' Make menu and tollbars visible. They appears in the Add-Ins tab
    Application.CommandBars("Old Menus").Visible = True
    Application.CommandBars("Old Menus2").Visible = True
End Sub[/blue]

 

[Nifrabar]

Strongm,

What do I wrong?
I cut and copied your code to create a macro.
When I ran that macro it throws errors starting with killmenu what seemed not te be defined.
also next line throws error.

Is it wrong to treat the code as macro?

-Bart

 

[strongm]

Whoops - sorry, forgot to give you the KillMenus procedure...

[blue]Public Sub KillMenus()
 '  Delete our current custom menus, if they exist
    On Error Resume Next
        Application.CommandBars("Old Menus").Delete
        Application.CommandBars("Old Menus2").Delete
    On Error GoTo 0
End Sub[/blue]

 

[Nifrabar]

StrongM,
Thanks.
This works in excel.
But when I try in msword than it throws an error at line:

With Application.CommandBars("Built-in Menus")

any idea?
-Bart

 

[strongm]

Okey dokey - I was just providing an alternative for the example you were previoulsy provided with, which was just for Excel.

Here is a minor modification to MakeOldMenus which works in both Excel and Word (just replace the Make OldMenus procudeure previoulsy provided with this one)

[blue]Public Sub MakeOldMenus()
    Dim OldMenu As CommandBar
    Dim OldMenu2 As CommandBar
    Dim lp As Long

    ' Delete our current custom menus, if they exist
    KillMenus

    ' Create an old-style toolbar
    ' Set the last argument to False for a (slightly) more compact menu
    Set OldMenu = Application.CommandBars.Add("Old Menus", , True)
    Set OldMenu2 = Application.CommandBars.Add("Old Menus2", , False)

    ' Now copy the old style menu items onto our custom menu
    ' using IDs to avoid language issues
    On Error Resume Next
    With Application.CommandBars
        .FindControl(, 30002).Copy OldMenu
        .FindControl(, 30003).Copy OldMenu
        .FindControl(, 30004).Copy OldMenu
        .FindControl(, 30005).Copy OldMenu
        .FindControl(, 30006).Copy OldMenu
        .FindControl(, 30007).Copy OldMenu
        .FindControl(, 30008).Copy OldMenu ' Does not exist in Excel - error handling will skip it
        .FindControl(, 30011).Copy OldMenu ' Does not exist in Word - error handling will skip it
        .FindControl(, 30009).Copy OldMenu
        .FindControl(, 30010).Copy OldMenu
    End With
    On Error GoTo 0
    ' For additional fun, add Standard and Formatting toolbars below menu
    ' Unfortunately we cannot display them in the ribbon in quite the same way as we might want
    For lp = 1 To Application.CommandBars("Standard").Controls.Count
        Application.CommandBars("Standard").Controls.Item(lp).Copy OldMenu2
    Next

    For lp = 1 To Application.CommandBars("Formatting").Controls.Count
        Application.CommandBars("Formatting").Controls.Item(lp).Copy OldMenu2
    Next

    ' Make menu and tollbars visible. They appears in the Add-Ins tab
    Application.CommandBars("Old Menus").Visible = True
    Application.CommandBars("Old Menus2").Visible = True
End Sub[/blue]

 

[GlennUK]

Thanks strongm, that's going into my useful things folder!

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Nifrabar]

Strongm,

Very very usefull.
Would there be on the net some documentation showing some more about the use of VBA for these matters?
Is the Add-In tab a standard location where this menu appears?
Can't we use VBA to move the ribbon away and just only have our classic menu?

By the way Star for youe good work!!

-Bart

 

[MakeItSo]

I just gave that a try and - DAMN - that's nice!!

Thanks a zillion, strongm!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[fumei]

Another star from me strongm!


unknown

 

[cmeagan656]

Nice work strongm! Thanks!

Hope this helps.

Please help us help you. Read Tek-Tips posting polices before posting.
Canadian members check out Tek-Tips in Canada for socializing, networking, and anything non-technical.

 

[xlbo]

Beautiful!

Having said that I must be one of the few people that actually prefer the ribbon...or at least the extra functionality that Excel 2010 has....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

The added functonality of Excel seems to be universally approved. No argument there. Plus it seems that Outlook 2010 is improved.

The ribbon though, I fail to see any advantage at all. With a plethora of disadvantage.


unknown

 

[strongm]

> actually prefer the ribbon

I like the ribbon as well (once I got used to it) - and it functions much like toolbars once you move to Office 2010