Old-Style Menus In Excel 2007
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
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"> <ribbon> <tabs> <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" /> </group> </tab> </tabs> </ribbon> </customUI>
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.
Identifying CommandBar Images
Excel 97 through Excel 2003 uses many images in its built in menus and toolbars. You can use these built-in images in your custom menus and toolbars by setting the FaceId property to a particular integer. The problem, however, is determining which integer corresponds to each image.
The subroutine below creates a custom toolbar with the first 250 FaceID images (shown below). You can see even more images by changing the values for IDStart and IDStop. The last FaceID image appears to be number 3518 (there are also many blank images).
After creating the toolbar, move the mouse pointer over a button to find out the FaceID value for the image. Clicking the toolbar buttons will have no effect since the subroutine doesn't assign any macros to the OnAction properties.
The ShowFaceIds Subroutine
Sub ShowFaceIDs() Dim NewToolbar As CommandBar Dim NewButton As CommandBarButton Dim i As Integer, IDStart As Integer, IDStop As Integer ' Delete existing FaceIds toolbar if it exists On Error Resume Next Application.CommandBars("FaceIds").Delete On Error GoTo 0 ' Add an empty toolbar Set NewToolbar = Application.CommandBars.Add _ (Name:="FaceIds", temporary:=True) NewToolbar.Visible = True ' Change the following values to see different FaceIDs IDStart = 1 IDStop = 250 For i = IDStart To IDStop Set NewButton = NewToolbar.Controls.Add _ (Type:=msoControlButton, Id:=2950) NewButton.FaceId = i NewButton.Caption = "FaceID = " & i Next i NewToolbar.Width = 600 End Sub
Creating Custom Menus
A companion file is available: Click here to download
Excel 97 through Excel 2003 use CommandBars for their menus. Workbook-specific menus must be created programmatically using VBA code. This tip describes a relatively simple way to create a custom menu (on the Worksheet Menu Bar) that appears when a particular workbook is opened, and is deleted when the workbook is closed.
Note: Although this technique works in Excel 2007, the results are not what you would expect. All custom CommandBars are displayed in the Add-Ins tab.
The example file contains all of the VBA code that you need to create your own custom menus. In most cases, you will not need to make any changes to the VBA code - simply customize the MenuSheet worksheet.
Please note that this technique will not work if you need to add a menu item to an existing menu.
How it works
This technique uses a table, which is stored in a worksheet. The figure below shows such a table. To create a custom menu, simply modify the data in the table.
This table contains five columns:
- Level: The "level" of the particular item. Valid values are 1, 2, and 3. A level of 1 is for a menu; 2 is for a menu item; and 3 is for a submenu item. Normally, you'll have one level 1 item, with level 2 items below it. A level 2 item may or may not have level 3 (submenus) items.
- Caption: The text that appears in the menu, menu item, or submenu. Use an ampersand (&) to specify a character that will be underlined.
- Position/Macro: For level 1 items, this should be an integer that represents the position in the menubar. For level 2 or level 3 items, this will be the macro that is executed when the item is selected. If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.
- Divider: True if a "divider" should be placed before the menu item or submenu item.
- FaceID: Optional. A code number that represents the built-in graphic images that are displayed next to an item.
An example menu
The figure below shows the menu that is created using the table above.
Using this technique
To use this technique in your workbook or add-in, follow these general steps:
- Download menumakr.xls (see link, above). This file contains the VBA code, plus a worksheet name MenuSheet.
- Copy all of the code in Module1 to a module in your project.
- Add subroutines like these to the code module for the ThisWorkbook object:
- The Workbook_Open subroutine is executed when the workbook is opened; the Workbook_BeforeClose subroutine is executed before the workbook is closed.
- Insert a new worksheet and name it MenuSheet. Better yet, copy the MenuSheet from the menumakr.xls file.
- Customize the MenuSheet to correspond to your custom menu.
- There is no error handling, so it's up to you to make sure that everything works.
Private Sub Workbook_Open() Call CreateMenu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteMenu End Sub
Developer FAQ - CommandBars
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
A companion file is available: Click here to download
I created an add-in that displays a new CommandBar that functions as a simple calculator. It may not be the most useful tool (it's limited to only the four basic mathematical operations), and I'll be the first to admit that it's fairly ugly. But it does demonstrate some useful VBA programming techniques.
The code is too lengthy to list here, but the VBA project for the add-in file is not protected so you can examine it.
NOTE: This add-in does not work with Excel 2007.
- Download the Toolbar Calculator add-in
- Start Excel and select the Tools - Add-Ins command.
- In the Add-Ins dialog box, click the Browse button and locate calctoolbar.xla.
- Click OK
- The toolbar will appear
- Select Tools - Toolbar Calculator to toggle the toolbar on and off.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
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