Visual Basic Macros to Add or Delete a Custom Menu

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.

   How do I add menus?