XL: How to Control Built-In and Custom Menus with Visual Basic

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, version 5.0

SUMMARY

You can use Microsoft Visual Basic for Applications to manipulate the built-in menus in Microsoft Excel or to replace them with custom menus designed by the user. This includes shortcut menus. This article describes how to do so.

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

Adding a Custom Menu

The following four Subroutines demonstrate how to add a menu called "Test" to the menu bar for all sheets in the current workbook.

NOTE: Type an ampersand (&) before the character you want to use as the access key. To create a separator bar, type a hyphen (-). In this example, when the menu is displayed, the letter "T" in "Test" will be underlined. You can use a letter for an access key only once per menu (that is, once you've used "T," you cannot use it again in that menu).

   Sub Add_Test_Menu()

   ' Searches through all the menus in the current workbook and deletes the
   ' menu "Test" if it already exists.

      For Each MB In MenuBars
         For Each MN In MB.Menus
            If MN.Caption = "&Test" Then
               MB.Menus("Test").Delete
            Else
            End If
         Next MN
      Next MB

   ' Cycles through all the menus in the current workbook.
      For Each MB In MenuBars

   ' Adds a top level menu called "Test" to each menu bar.
         MB.Menus.Add Caption:="&Test"

   ' Adds a submenu called "Test 1" under the menu "Test."
         MB.Menus("Test").MenuItems.AddMenu Caption:="&Test 1"

   ' Adds menu items "Test 2," "Test 3," and "Delete This Menu," under the
   ' submenu "Test 1" and assigns the macros that should be run when each 
   ' is selected.
         MB.Menus("Test").MenuItems("Test 1").MenuItems.Add Caption:= _
            "Test 2", OnAction:="Test2"
         MB.Menus("Test").MenuItems("Test 1").MenuItems.Add Caption:= _
            "Test 3", OnAction:="Test3"
         MB.Menus("Test").MenuItems.Add Caption:="Delete This Menu", _
            OnAction:="Delete_Menu"
      Next
   End Sub

   ' This subroutine is run by submenu "Test 2."
   Sub Test2()
      MsgBox "You Chose Test 2"
   End Sub

   ' This subroutine is run by submenu "Test 3."
   Sub Test3()
      MsgBox "You Chose Test 3"
   End Sub

   ' This subroutine is run by submenu "Delete This Menu."
   Sub Delete_Menu()
      For Each MB In MenuBars
         MB.Menus("Test").Delete
      Next
   End Sub

Replacing Built-In Menus With Custom Menus

By using Visual Basic for Applications, it is also possible to build your own complete menu bar and use it, instead of the built-in menu bars. The next five subroutines show an example of how to do this:

   Sub New_Menu_Bar()

      ' Creates a menu bar named "Test."
      MenuBars.Add "Test"

      ' Adds menu items "Files" and "Edit" to menu bar "Test."
      MenuBars("Test").Menus.Add Caption:="&Files"
      MenuBars("Test").Menus.Add Caption:="Edit"

      ' Adds menu items "New," "Open," and "Save" under the "Files menu 
      ' item.
      MenuBars("Test").Menus("&Files").MenuItems.Add Caption:="New", _
         OnAction:="Menu_New"
      MenuBars("Test").Menus("&Files").MenuItems.Add Caption:="Open", _
         OnAction:="Menu_Open"
      MenuBars("Test").Menus("&Files").MenuItems.Add Caption:="Save", _
         OnAction:="Menu_Save"

      ' Adds menu item "Restore Original" under the "Edit" menu item.
      MenuBars("Test").Menus("Edit").MenuItems.Add Caption:= _
         "Restore Original", OnAction:="Restore_Original_Menu"

      ' Displays the "Test" menu bar.
      MenuBars("Test").Activate

   End Sub

   ' This subroutine is run by submenu "New."
   Sub Menu_New()
      MsgBox "Your own code for the New menu option would go here."
   End Sub

   ' This subroutine is run by submenu "Open."
   Sub Menu_Open()
      MsgBox "Your own code for the Open menu option would go here."
   End Sub

   ' This subroutine is run by submenu "Save."
   Sub Menu_Save()
      MsgBox "Your own code for the Save menu option would go here."
   End Sub

   ' This subroutine is run by submenu "Restore Original."
   Sub Restore_Original_Menu()

   ' NOTE: In the next line, use xlWorksheet, xlModule, or xlChart 
   ' depending on what type of sheet is active when this subroutine is run.

      MenuBars(xlModule).Activate

   ' Deletes the custom menu bar "Test."
      MenuBars("Test").Delete

   End Sub

By using Visual Basic for Applications, you can also customize the Microsoft Excel built-in shortcut menus, the menus displayed by using the right mouse button to click an object in Windows or by holding down the CTRL key and clicking an object on a Macintosh. An example would be a cell. The next five subroutines show an example of how to do this.

   Sub Add_To_ShortCut()

      ' Defines the object SCM to be the built in Excel worksheet shortcut
      ' menu.
      Set SCM = Application.ShortcutMenus(xlWorksheetCell)

      ' Adds a separator bar to the worksheet shortcut menu.
      SCM.MenuItems.Add Caption:="-"

      ' Adds menu "My Menu" to the worksheet shortcut menu.
      SCM.MenuItems.AddMenu "My Menu"

      ' Adds "Test 1," "Test 2,"and "Test 3" as submenus of "My Menu."
      SCM.MenuItems("My Menu").MenuItems.Add Caption:="Test 1", _
         OnAction:="Test_1"
      SCM.MenuItems("My Menu").MenuItems.Add Caption:="Test 2", _
         OnAction:="Test_2"
      SCM.MenuItems("My Menu").MenuItems.Add Caption:="Test 3", _
         OnAction:="Test_3"

      ' Adds another separator bar to the worksheet shortcut menu.
      SCM.MenuItems.Add Caption:="-"

      ' Adds "Remove Menu" to the worksheet shortcut menu.
      SCM.MenuItems.Add Caption:="Remove My Menu", OnAction:="Remove_Menu"

   End Sub

   ' This subroutine is run by the "Test 1" menu.
   Sub Test_1()
      MsgBox "This would be your macro for Test 1."
   End Sub

   ' This subroutine is run by the "Test 2" menu.
   Sub Test_2()
      MsgBox "This would be your macro for Test 2."
   End Sub

   ' This subroutine is run by the "Test 3" menu.
   Sub Test_3()
      MsgBox "This would be your macro for Test 3."
   End Sub

   ' This subroutine is run by the "Remove Menu" menu.
   Sub Remove_Menu()

      ' Defines the object SCM to be the built in Excel worksheet shortcut
      ' menu.
      Set SCM = Application.ShortcutMenus(xlWorksheetCell)

      ' Deletes the third separator bar down from the top of the worksheet
      ' shortcut menu. The number of dashes corresponds to number of 
      ' separators from the top you wish to use. If you have not added any 
      ' other custom menus, this should be the first one you added with the
      ' "Add_To_ShortCut" subroutine.
      SCM.MenuItems("---").Delete

      ' Deletes "My Menu."
      SCM.MenuItems("My Menu").Delete

      ' Deletes the third separator bar down from the top of the worksheet
      ' shortcut menu. Even though you added two separators, when you 
      ' deleted the third one, the fourth moved up into its place, so we 
      ' are deleting the third one again.

      SCM.MenuItems("---").Delete

      ' Deletes "Remove Menu".
      SCM.MenuItems("Remove My Menu").Delete

   End Sub

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 12, pp. 241-256

For additional information on working with menus in Microsoft Excel, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q137133
   TITLE     : WE1182: Customizing Menu Bars, Menus, and Menu Items

   creating new menus and submenus