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:
XL97: Sample Macros for Customizing Menus and Submenus
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
' The following line of code adds "Test" as a new menu on
' the worksheet menu bar.
' The following line of code adds "Submenu" as a new menu item
' on the Test menu.
' Set mymenu to be the menu items under Submenu.
Set mymenu = Application.MenuBars(xlWorksheet) _
.Add "Item1" 'Adds Item1 to Submenu
.Add "Item2" 'Adds Item2 to Submenu
.Add "Item3" 'Adds Item3 to Submenu
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:
' The following line of macro code removes the
' "Test" menu from the worksheets menu bar.
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
- 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.
In Microsoft Excel version 7.0, for more information about creating the
Shell function, click Answer Wizard on the Help menu and type:
Microsoft Press: "Microsoft Excel Visual Basic Reference," Second Edition,
"Visual Basic User's Guide," version 5.0, Chapter 12, "Managing Menus with
Article ID: 141688 - Last Review: January 12, 2015 - Revision: 3.0
- Microsoft Excel 95 Standard Edition
|kbnosurvey kbarchive kbcode kbhowto kbprogramming KB141688|