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">
<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

Category: CommandBars & Menus | [Item URL]

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

Category: CommandBars & Menus | [Item URL]

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:

1. Download menumakr.xls (see link, above). This file contains the VBA code, plus a worksheet name MenuSheet.
2. Copy all of the code in Module1 to a module in your project.
3. Add subroutines like these to the code module for the ThisWorkbook object:

Private Sub Workbook_Open()
   Call CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
End Sub

4. The Workbook_Open subroutine is executed when the workbook is opened; the Workbook_BeforeClose subroutine is executed before the workbook is closed.
5. Insert a new worksheet and name it MenuSheet. Better yet, copy the MenuSheet from the menumakr.xls file.
6. Customize the MenuSheet to correspond to your custom menu.
7. There is no error handling, so it's up to you to make sure that everything works.

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

CommandBar Calculator

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

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.

Installation:

1. Download the Toolbar Calculator add-in
2. Start Excel and select the Tools - Add-Ins command. 
3. In the Add-Ins dialog box, click the Browse button and locate calctoolbar.xla.
4. Click OK
5. The toolbar will appear
6. Select Tools - Toolbar Calculator to toggle the toolbar on and off.