Microsoft KB Archive/137133

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c

-

SUMMARY
The Application Note "Customizing Menu Bars, Menus, and Menu Items" (WE1182) is now available from Microsoft Product Support Services. This Application Note can help you learn some techniques for writing Visual Basic, Applications Edition, code for use with custom menus. This Application Note is meant to be used as a supplement to Chapter 12 ("Menus and Toolbars") of the "Visual Basic User's Guide." The Vba_mnu.xls file included with this Application Note contains code examples you can run. These code examples can be used with the following elements: menu bars, menu items, submenus, and shortcut menus.

MORE INFORMATION
The following file is available for download from the Microsoft Download Center. Click the file name below to download the file:

"We1182.exe" For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address "http://www.microsoft.com/downloads/search.asp" and then click How to use the Microsoft Download Center.

THE TEXT OF WE1182
====================================================================== Microsoft(R) Product Support Services Application Note (Text File) WE1182: CUSTOMIZING MENU BARS, MENUS, AND MENU ITEMS

=
=========================================================                                                  Revision Date: 9/95 1 Disk Included

The following information applies to Microsoft Excel, version 5.0, 5.0c.

- INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY ACCOMPANY THIS DOCUMENT (collectively referred to as an Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. The user assumes the entire risk as to the accuracy and the use of this Application Note. This Application Note may be copied and distributed subject to the following conditions: 1) All text must be copied without modification and all pages must be included;  2) If software is included, all files on the disk(s) must be copied without modification (the MS-DOS(R) utility diskcopy is appropriate for this purpose);  3) All components of this Application Note must be distributed together;  and  4) This Application Note may not be distributed for profit.

Copyright (C) 1995 Microsoft Corporation. All Rights Reserved. Microsoft, MS-DOS, Visual Basic and Windows are registered trademarks of Microsoft Corporation. -

TABLE OF CONTENTS =================

Overview

How to Use the Examples in This Application Note Using the Vba_mnu.xls File To install the Vba_mnu.xls file on your hard disk To use Vba_mnu.xls Using the Examples in the Text of This Application Note

Visual Basic Code Examples and Tips Menu Bars To save the active menu bar state (either built-in or     customized) Menus To count the number of menus on the worksheet menu bar Menu Items To add a separator bar menu item To add a shortcut key to a menu item To assign a Help file to a menu item Submenus To add a submenu To delete a submenu Shortcut Menu Bars Shortcut Menus To add a menu item to the "Shortcut menu 1" menu bar To delete a menu item from a shortcut menu bar To restore a menu item on built-in shortcut menu bar

Where to Find More Information The Object Browser Microsoft Knowledge Base FastTips System

OVERVIEW =========

While many of the common tasks associated with customizing menu bars and menus can be accomplished with the Menu Editor (with a module sheet active, click Menu Editor on the Options menu), in order to get further functionality from menu bars and menus, or to tailor them for a custom-built application, you may want to create Visual Basic(R), Applications Edition, procedures.

For more information about the Menu Editor, see pages 242-248 of the "Visual Basic User's Guide."

This Application Note can help you learn some techniques for writing Visual Basic, Applications Edition, code for use with custom menus. This Application Note is meant to be used as a supplement to Chapter 12 ("Menus and Toolbars") of the "Visual Basic User's Guide." The Vba_mnu.xls file included with this Application Note contains code examples you can run. These code examples can be used with the following elements: menu bars, menu items, submenus, and shortcut menus.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. These Visual Basic procedures are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. Microsoft does not support modifications of these procedures to suit customer requirements for a particular purpose.

HOW TO USE THE EXAMPLES IN THIS APPLICATION NOTE =================================================

USING THE VBA_MNU.XLS FILE

=
=============

To use the Vba_mnu.xls file, copy it to a folder on your hard disk, and open it in Microsoft Excel.

To install the Vba_mnu.xls file on your hard disk -

1. If you received a disk with this Application Note, insert the disk in the appropriate floppy disk drive. If you downloaded this Application Note from an online service, skip to step 2. This procedure assumes that you have already downloaded and extracted the compressed file.

2. Copy the Vba_mnu.xls file from the WE1182 disk (or from the folder  to which you downloaded and extracted the We1182.exe file) to the appropriate folder on your hard disk.

For more information about copying files, see your Windows "User's Guide" or Windows online Help.

To use Vba_mnu.xls --

1. Start Microsoft Excel, and click Open on the File menu.

2. Select the folder to which you installed the Vba_mnu.xls file, select Vba_mnu.xls, and click Open.

3. To run this example, click Menu Example on the Tools menu.

4. To quit the example, click Restore Menus on the ResetMenu menu.

USING THE EXAMPLES IN THE TEXT OF THIS APPLICATION NOTE

=
==========================================

The examples in the text portion of this Application Note demonstrate ways you can use Visual Basic, Applications Edition, code to accomplish basic tasks with menus.

VISUAL BASIC CODE EXAMPLES AND TIPS ===================================

MENU BARS

=
Menu bars are the objects on which menus, menu items, and submenus are built.

See this page of                                           the "Visual Basic For information about this topic           User's Guide"

To add a new menu bar                     243-244, 250 To activate a created or built-in menu bar 250 To delete a custom menu bar               251 To determine whether a menu is built-in   250 To count the number of menu bars available 249 To restore all built-in menu bars, menus, 251 and menu items

For a list of the built-in menu bars included in Microsoft Excel 5.0, see page 244 of the "Visual Basic User's Guide." The following table lists the menu bars that are included in Microsoft Excel 5.0 to maintain compatibility with earlier versions of Microsoft Excel. These menu bars are not listed in the "User's Guide." (To display these menus, click Options on the Tools menu, and select Microsoft Excel version 4.0 menus on the General tab.)

Built in menu bar name         Description -

Worksheet (XL4)       The menu bar that appears when a                        Microsoft Excel 4.0 worksheet is active.

Chart (XL4)           The menu bar that appears when a                        Microsoft Excel 4.0 chart is active.

Worksheet (XL4,       The menu bar that appears when you use Short)                 the right mouse button to click an object in a Microsoft Excel 4.0 worksheet.

Chart (XL4,           The menu bar that appears when you use Short)                 the right mouse button to click an object in a Microsoft Excel 4.0 chart.

For information about Constants to use with built-in menu bars, see pages 249-250 of the "Visual Basic User's Guide." Constants that are not included in the "User's Guide" are listed in the following table.

This constant     Is associated with this menu bar ---

xlWorksheet4      Worksheet (XL4)

xlChart4          Chart (XL4)

xlWorksheetShort  Worksheet (XL4, Short)

xlShortChart      Chart (XL4, Short)

To save the active menu bar state (either built-in or customized) -

You may want to declare "oldMenuBar" as a public variable so that another subroutine can use it in an Auto_Close subroutine. Declaring and using the variable this way will reset the user's previous menu bar to its original state.

Public oldMenuBar

Sub MenuBars_Ex4 oldMenuBar = ActiveMenuBar.Index End Sub

To determine the name of the active menu bar

Sub MenuBars_Ex5 MsgBox ActiveMenuBar.Caption End Sub

MENUS

=
Menus are added to either a custom or built-in menu bar.

See this page of the For information about this topic           "Visual Basic User's Guide" ---

To add a menu to the worksheet menu bar     252

To delete a menu from the worksheet menu    252 bar

To restore a deleted built-in menu on the   252 worksheet menu bar

To disable a menu on the worksheet menu     252 bar

To count the number of menus on the worksheet menu bar --

Sub Menu_Ex5 MsgBox MenuBars(xlWorksheet).Menus.Count End Sub

MENU ITEMS

=
Menu items can be added to or removed from either built-in or custom menus. They usually have an access key, and many of the built-in menu items contain a shortcut key. Menu items that have an ellipsis (...) following the caption denote a menu item that opens a dialog box. Menu items that have a small black arrow after the caption will display one or more submenu items. See this page of the For information about this topic           "Visual Basic User's Guide" ---

To add a menu item to an existing          253 worksheet menu

To delete a menu item                      253

To disable a custom menu item              254

NOTE: Built-in menu items cannot be disabled, only deleted.

To add a check mark to a menu item         254

To add a separator bar menu item

To add a separator bar to a menu, use a hyphen in place of the caption text of a menu item.

Sub menuitem_Ex4 MenuBars(xlWorksheet).Menus("&Edit").MenuItems.Add _ Caption:="-", Before:="Delete Sheet" End Sub

To add a shortcut key to a menu item

This macro adds a menu item called "Command1" with a CTRL+K shortcut key to the Edit menu on the worksheet menu bar. It also specifies that the "myShortCutExample" procedure is run when you press CTRL+K.

Sub menuItem_Ex5 With Application.MenuBars(xlWorksheet).Menus("&Edit") .MenuItems.Add Caption:="Command&1     Ctrl+K", _ OnAction:="mnuShortCutKey", _ StatusBar:="Short-cut key on a MenuItem" Application.OnKey "^k", "myShortCutExample" End With End Sub

To assign a Help file to a menu item

The following macro adds a menu item called "Visual Basic Help" to the Help menu on the module sheet menu bar. The OnAction property runs the Open_VisualBasic_Help macro, which opens the Help file.

Note: The StatusBar property is only available on Excel 5.0c and later.

Sub menuItem_Ex7 With Application.MenuBars(xlModule).Menus("&Help") .MenuItems.Add _ Caption:="Visual Basic Help", _ OnAction:="Open_VisualBasic_Help", _ Before:=1, _ StatusBar:="Opens Excel Visual Basic Help" End With End Sub

This macro opens the Contents topic of the Visual Basic Help file when you select the "Visual Basic Help" menu item.

Sub Open_VisualBasic_Help Application.Help Application.Path & "\vba_xl.hlp", 65535 End Sub

SUBMENUS

=
Submenus appear off to the side when a menu item is selected. A menu item that has a submenu has a small black arrow located at the right end of the menu item.

To add a submenu

This macro adds a menu item called "Command1" to the Edit menu (on the worksheet menu bar). It then adds two submenu items to the Command1 menu item; both of these submenu items run a procedure called "subMenuMacro."

Sub SubMenu_Ex1 With Application.MenuBars(xlWorksheet).Menus("&Edit") .MenuItems.AddMenu Caption:="Command1" .MenuItems("Command1").MenuItems.Add _ Caption:="Added SubMenu 1", _ OnAction:="subMenuMacro" .MenuItems("Command1").MenuItems.Add _ Caption:="Added SubMenu 2", _ OnAction:="subMenuMacro" End With End Sub

The subMenuMacro macro returns the caption text of the submenu when either "Added SubMenu 1" or "Added SubMenu 2" is selected.

Sub subMenuMacro Dim theSubMenu As String theSubMenu = Application.Caller With Application.MenuBars(xlWorksheet).Menus("Edit") MsgBox .MenuItems("Command1").MenuItems(theSubMenu).Caption End With End Sub

To delete a submenu ---

This macro deletes all of the submenus from the Fill menu item on the Edit menu (on the worksheet menu bar).

Sub SubMenu_Ex2

Dim subMnuItm As Object  SHORTCUT MENU BARS

=
=====

Shortcut menu bars appear when you use the right mouse button to click a specific Microsoft Excel object. Microsoft Excel has three shortcut menu bars that each have a variety of menus available. You cannot create custom shortcut menu bars, although the built-in menu bars are fully customizable. The following table lists all of the associated menus that are available for each shortcut menu bar.

Shortcut      Shortcut       Shortcut Menus 1       Menus 2        Menus 3 ---

Toolbar       Drawing        Chart Series Object Toolbar       Button         Chart Text Button Worksheet     Text Box       Chart Plot Cell                         Area Column        Dialog Sheet   Entire Chart Row                          Chart Axis Workbook Tab                 Chart Gridline Macro Sheet                  Chart Floor Cell Title Bar                    Chart Legend Desktop Module Watch Pane Immediate Pane Debug Code Pane

SHORTCUT MENUS

=
=

To add a menu item to the "Shortcut menu 1" menu bar

This macro adds a menu item to the shortcut menu that is available when the user is selecting a cell on a worksheet.

Sub ShortCut_Ex1 Application.MenuBars("Shortcut Menus 1") _ .Menus("Worksheet Cell").MenuItems.Add _ Caption:="Add 20%", _ OnAction:="Add_Percent", _ Before:=1 End Sub

The Add_Percent macro is run when the "Add 20%" menu item is selected in the preceding shortcut menu bar example.

Sub Add_Percent Dim temp As Variant temp = ActiveCell.Value If IsNumeric(temp) Then ActiveCell.Value = temp * 1.2 End Sub

To delete a menu item from a shortcut menu bar --

Sub Shortcut_Ex2 With Application.MenuBars("Shortcut Menus   1").Menus("Worksheet Cell") .MenuItems("Clear Contents").Delete End With End Sub

To restore a menu item on built-in shortcut menu bar

This macro restores the "Clear Contents" menu item that was deleted in the example above.

Sub Shortcut_Ex3 With Application.MenuBars("Shortcut Menus 1") .Menus("Worksheet Cell").MenuItems.Add _ Caption:="Clear Contents", _ Before:=8, _ Restore:=True End With End Sub

WHERE TO FIND MORE INFORMATION

=
=================

THE OBJECT BROWSER --

A complete list of all of the properties and methods for a specific menu item is available in the Object Browser. To find this information, switch to a Visual Basic module, click Object Browser on the View menu, and then select the name of the control you want from the Excel Libraries/Workbooks list.

For more information about using the Object Browser, see pages 77-79 of the "Visual Basic User's Guide."

MICROSOFT KNOWLEDGE BASE

The Microsoft Knowledge Base is a primary Microsoft product information source for Microsoft Support professionals and is also available to Microsoft customers. This comprehensive database contains more than 40,000 detailed articles with technical information about Microsoft products, fix lists, documentation errors, and answers to commonly asked technical support questions. These articles are also available through The Microsoft Network, the Microsoft TechNet CD-ROM, and the Microsoft Developer Network CD-ROM.

FASTTIPS SYSTEM ---

Microsoft FastTips is an automated, toll-free service that provides technical information about key Microsoft products and is available 24 hours a day, 7 days a week in the United States and Canada. Using the FastTips system, you can receive automated answers to common technical problems and access popular articles from the Microsoft Knowledge Base. This information is delivered over the phone through recorded voice scripts, by fax, or through the U.S. mail.

Home Products FastTips                (800) 936-4100 Desktop Applications FastTips         (800) 936-4100 Desktop Systems FastTips              (800) 936-4200 Development Tools FastTips            (800) 936-4300 Business Systems FastTips             (800) 936-4400

-- Additional query words: 5.00c appnote

Keywords : kbfile kbprg kbdta xlvbainfo xlvbainf xlwin

Version : WINDOWS:5.0,5.0c

Platform : WINDOWS

Issue type : kbhowto
 * }