XL: Visual Basic Macros to Add or Delete a Custom Menu

• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows, version 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0

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:

   ARTICLE-ID: Q159619
   TITLE     : XL97: Sample Macros to Control Menus and Submenus

MORE INFORMATION

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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?

"Visual Basic User's Guide," version 5.0, Chapter 12, "Managing Menus with Visual Basic"