Microsoft KB Archive/147300

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

Article ID: 147300

Article Last Modified on 6/11/2007

-

APPLIES TO


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

-



This article was previously published under Q147300



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. 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.

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

Adding a Custom Menu Item to a Shortcut Menu
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

