Old-Style Menus In Excel 2007

Category: General / CommandBars & Menus | [Item URL]

Here's a quick way to display the old-style menu in Excel 2007 or later. Execute this VBA procedure, and you'll get a toolbar that contains the old Excel 2003 menu commands.

Sub MakeOldMenus()
   Dim cb As CommandBar
   Dim cbc As CommandBarControl
   Dim OldMenu As CommandBar

'  Delete it, if it exists
   On Error Resume Next
   Application.CommandBars("Old Menus").Delete
   On Error GoTo 0

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

'  Copy the controls from Excel's "Built-in Menus" shortcut menu
   With CommandBars("Built-in Menus")
     .Controls("&File").Copy OldMenu
     .Controls("&Edit").Copy OldMenu
     .Controls("&View").Copy OldMenu
     .Controls("&Insert").Copy OldMenu
     .Controls("F&ormat").Copy OldMenu
     .Controls("&Tools").Copy OldMenu
     .Controls("&Data").Copy OldMenu
     .Controls("&Window").Copy OldMenu
     .Controls("&Help").Copy OldMenu
   End With

'   Make it visible. It appears in the Add-Ins tab
   Application.CommandBars("Old Menus").Visible = True
End Sub

After you execute this macro, the new menu appears in the Add-Ins tab:

The menu isn't perfect. A few of the command don't work, and the list of recent files in the File menu just shows placeholders.

Add The Speech Controls To The Ribbon

Category: CommandBars & Menus | [Item URL]

A companion file is available: Click here to download

For some reason, the text-to-speech controls are not included in the Excel 2007 ribbon UI. Those who want to use these tools must add them to their Quick Access Toolbar.

This tip describes how to create a simple add-in that, when installed, displays the text-to-speech controls in the Review tab of the ribbon. These controls are in a new group (labeled Text to Speech), which is positioned between the Proofing group and the Comments group.

The new ribbon group contains six controls. The first five are built-in commands, so they don't require any callbacks. The last one, Voice, is a custom control. When clicked, it executes a VBA procedure that displays the Speech Properties dialog box from the Windows Control Panel. This dialog box lets you choose a different voice and control the speed of the voice.

I used the Custom UI Editor Tool to add the following RibbonX code that displays the new ribbon group:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<tab idMso="TabReview">
<group id="SpeechGroup" label="Text to Speech" insertBeforeMso="GroupComments">
<control idMso="SpeakCells" size="large" />
<control idMso="SpeakStop" size="large" />
<separator id="sep1" />
<control idMso="SpeakOnEnter" size="normal" />
<control idMso="SpeakByRows" size="normal" />
<control idMso="SpeakByColumns" size="normal" />
<button id ="RunSapi" label="Voice" imageMso = "DirectRepliesTo" onAction="RunSapiApp" />

The VBA procedure that is executed when the Voice button is clicked is defined like this:

Sub RunSapiApp(control As IRibbonControl)
 ' code here
End Sub

As it turns out, displaying the Sapi.cpl Control Panel dialog box is not as simple as displaying other control panel dialogs -- and I'm not certain that it will work on all systems. If you're interested, you can download the add-in and view the code.

Developer FAQ - CommandBars

Category: CommandBars & Menus | [Item URL]

Note: This document was written for Excel 97 - Excel 2000

I have a macro attached to a toolbar button. Is it possible to have the macro perform a different action if the user presses Shift while the button is clicked?

Yes, but you have to use a Windows API call to detect if the Shift key is pressed.

Excel 95 had a handy menu editor, but it's missing in Excel 97 and Excel 2000. What gives?

Beginning with Excel 97, the toolbars and menus in Excel are entirely different. Both are called CommandBars. The menu editor is gone, but users can edit CommandBars using the Customize dialog box (select Tools- Customize).

Is there an easy way to create a new menu on the fly?

Yes. Follow this link.

When I change a menu using the Customize dialog box, the menu is changed permanently. How can I make the menu change apply only to one workbook?

You'll need to perform you menu changes (using VBA code) when the workbook is opened, and restore the menu to normal when the workbook is closed.

I know you can use the FaceId property to add an image to a toolbar control. But how do I figure out which FaceID value goes with a particular image?

Microsoft didn't provide any way to do this, but several utilities exist that make it easy to identify the FaceID values. Follow this link to download such a utility.

I attached a new version of my toolbar to a workbook, but Excel continues to use the older version. How do I get it to use the new version of my toolbar?

When Excel opens a workbook that has an attached toolbar, it displays the toolbar only if one with the same name does not already exist on the user's system. The best solution is to write VBA code to create the toolbar on the fly when the workbook is opened, and delete it when the workbook is closed.

I've made lots of changes to Excel's toolbars. How can I restore all of these toolbars to their original state?

You can use the Customize dialog box and reset each one manually. Or, run the subroutine listed below.

Sub ResetAllToolbars()
    For Each tb In CommandBars
        If tb.Type = msoBarTypeNormal Then
            If tb.BuiltIn Then tb.Reset
        End If
    Next tb
End Sub

How can I set things up so my custom menu is displayed only when a particular workbook is active?

You need to make use of the WorkbookActivate and WorkbookDeactivate events. In other words, write subroutines (stored in the code module for the ThisWorkbook object) that hide the custom menu when the workbook is deactivated, and unhide the custom menu when the workbook is activated.

How can I add a "spacer" between two buttons on a toolbar?

Set the BeginGroup property of the control to True.

How do you make a menu item have a checkmark next to it?

A checkmark on a menu item is controled by the State property. The statement below, for example, displays a checkmark next to

CommandBars(1).Commands("MyMenu"). _
    Commands("My Item").State = msoButtonDown

To uncheck the menu item, set the State property to msoButtonUp

I accidentally deleted some items from the Worksheet menu and can't get them back. Restarting Excel doesn't fix it.

Select Tools - Customize and select the Toolbars tab in the Customize dialog box. Select the Worksheet Menu Bar item and click the Reset button.

How can I disable all of the right-click shortcut menus?

The subroutine below will do the job.

Sub DisableAllShortcutMenus()
    Dim cb As CommandBar
    For Each cb In CommandBars
        If cb.Type = msoBarTypePopup Then cb.Enabled = False
    Next cb
End Sub

Is there a way to disable the shortcut menus that appear when the user clicks the right mouse button?

Yes, the statement below will do the job:

CommandBars("Toolbar List").Enabled = False

The statement listed in the previous question doesn't work!

The original version of Excel 97 had a problem with this statement. It was corrected in the SR-1 service release for Excel 97

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy