Visual Basic Macros to Add or Delete a Custom Menu (141688)


The information in this article applies to:

  • Microsoft Excel for Windows 95
  • Microsoft Excel for Windows 5.0c
  • Microsoft Excel for the Macintosh 5.0a
This article was previously published under Q141688

SUMMARY

In Microsoft Excel, you can use a Microsoft Visual Basic for Applications macro to add and remove custom menus and menu items.

For information about how to do this in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition, please see the following article in the Microsoft Knowledge Base:

159619 XL97: Sample Macros for Customizing Menus and Submenus

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Macro to Add a Menu and a Menu Item

The following macro demonstrates how to use the Add method to add menus and menu items. 
   Sub Add_Menu()

      Dim mymenu

      ' The following line of code adds "Test" as a new menu on
      ' the worksheet menu bar.
      Application.MenuBars(xlWorksheet).Menus.Add "Test"

      ' The following line of code adds "Submenu" as a new menu item
      ' on the Test menu.
      Application.MenuBars(xlWorksheet).Menus("Test").MenuItems.AddMenu _
         "SubMenu"

      ' Set mymenu to be the menu items under Submenu.
      Set mymenu = Application.MenuBars(xlWorksheet) _
         .Menus("Test").MenuItems("SubMenu").MenuItems

      With mymenu
         .Add "Item1"  'Adds Item1 to Submenu
         .Add "Item2"  'Adds Item2 to Submenu
         .Add "Item3"  'Adds Item3 to Submenu
      End With

   End Sub

Macro to Delete a Menu

To delete a menu from a menubar, use the Delete method. The macro example below demonstrates how to delete a menu: 
   Sub Del_Menu()

      ' The following line of macro code removes the
      ' "Test" menu from the worksheets menu bar.
      Application.MenuBars(xlWorksheet).Menus("Test").Delete

   End Sub

Notes About the Sample Macros

  • The Add method of the Menu items Collection adds a new menu item to the specified menu. It can also be used to restore one of the Microsoft Excel built-in menu items which has been deleted.
  • The Caption argument is required, and it specifies the name of the menu item to add.
  • Using an ampersand (&) before any specific letter of the Caption will underline that letter, allowing it to be used with the ALT key as a keyboard shortcut.
  • Using a single hyphen ("-") for the caption will create a separator bar.
  • The OnAction argument of the Add Method is optional, and it specifies the name of the macro that is run when the new menu item is selected.
  • The StatusBar argument is also optional, and it specifies help text to be displayed in the status bar when the menu item is selected or browsed by the user. If the StatusBar argument is omitted, the default status bar text assigned to the macro is used.
  • The optional HelpFile argument specifies the help file name containing the custom help topic of the menu item. If omitted, the default help file name assigned to the macro is used.

REFERENCES

In Microsoft Excel version 7.0, for more information about creating the Shell function, click Answer Wizard on the Help menu and type: 
   How do I add menus?
Microsoft Press: "Microsoft Excel Visual Basic Reference," Second Edition, pages 32-35

"Visual Basic User's Guide," version 5.0, Chapter 12, "Managing Menus with Visual Basic"
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbcode kbhowto kbProgramming KB141688
©2004 Microsoft Corporation. All rights reserved.