WE1182: "Customizing Menu Bars, Menus, and Menu Items"

• 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

You can obtain this Application Note from the following sources:

• Microsoft's World Wide Web Site on the Internet
• The Internet (Microsoft anonymous ftp server)
• The Microsoft Network (MSN)
• Microsoft Download Service (MSDL)
• Microsoft Product Support Services

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

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

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.

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

 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)

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

   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

   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

      For Each subMnuItm In
   Application.MenuBars(xlWorksheet).Menus("&Edit"). _
         MenuItems("&Fill").MenuItems
         subMnuItm.Delete
      Next subMnuItm

   End Sub

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

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

TO OBTAIN THIS APPLICATION NOTE

The following file(s) are 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

   http://www.microsoft.com/worldwide/default.htm