WE1182: "Customizing Menu Bars, Menus, and Menu Items"
ID: Q137133
|
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