Microsoft KB Archive/141688

= Visual Basic Macros to Add or Delete a Custom Menu =

Article ID: 141688

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



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.

