ID: Q137133
The information in this article applies to:
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.
The following file is available for download from the Microsoft Software Library:
~ WE1182.exe (size: 66326 bytes)
ARTICLE-ID: Q119591
TITLE : How to Obtain Microsoft Support Files from Online Services
Microsoft(R) Product Support Services Application Note (Text File)
WE1182: CUSTOMIZING MENU BARS, MENUS, AND MENU ITEMS
Revision Date: 9/95
1 Disk Included
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
=================
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
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
The Object Browser
Microsoft Knowledge Base
FastTips System
OVERVIEW
=========
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
=================================================
To use the Vba_mnu.xls file, copy it to a folder on your hard disk, and open it in Microsoft Excel.
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.
to which you downloaded and extracted the We1182.exe file) to the
appropriate folder on your hard disk.
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.
4. To quit the example, click Restore Menus on the ResetMenu menu.
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 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
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.
This constant Is associated with this menu bar
---------------------------------------------------
xlWorksheet4 Worksheet (XL4)
xlChart4 Chart (XL4)
xlWorksheetShort Worksheet (XL4, Short)
xlShortChart Chart (XL4, Short)
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
Sub MenuBars_Ex5()
MsgBox ActiveMenuBar.Caption
End Sub
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
Sub Menu_Ex5()
MsgBox MenuBars(xlWorksheet).Menus.Count
End Sub
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 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
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
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
Sub Open_VisualBasic_Help()
Application.Help Application.Path & "\vba_xl.hlp", 65535
End Sub
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.
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
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
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
For Each subMnuItm In
Application.MenuBars(xlWorksheet).Menus("&Edit"). _
MenuItems("&Fill").MenuItems
subMnuItm.Delete
Next subMnuItm
End Sub
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
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
Sub Add_Percent()
Dim temp As Variant
temp = ActiveCell.Value
If IsNumeric(temp) Then ActiveCell.Value = temp * 1.2
End Sub
Sub Shortcut_Ex2()
With Application.MenuBars("Shortcut Menus
1").Menus("Worksheet Cell")
.MenuItems("Clear Contents").Delete
End With
End Sub
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
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."
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.
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.00 5.00c appnote
Keywords : kbprg kbdta xlvbainfo xlvbainf xlwin
Version : 5.00 5.00c
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: May 19, 1999