Microsoft KB Archive/830502

= How to customize menus and menu bars in Excel =

Article ID: 830502

Article Last Modified on 1/11/2006

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-





SUMMARY
''This article describes how to customize menus and menu bars in Microsoft Excel 2000 and later. This article contains step-by-step instructions and code samples to programmatically manage and customize menu bars, menus, commands, submenus, and shortcut menus in Microsoft Excel.''



IN THIS TASK

 * INTRODUCTION
 * MORE INFORMATION
 * Examples in this article
 * Command bars
 * Control constants
 * Menu bars
 * Return an ID for a command bar control
 * Determine the name of the active menu bar
 * Save the active state (for built-in or for customized menu bars)
 * Create a custom command bar
 * Display a custom command bar
 * Delete a custom command bar
 * Hide a command bar
 * Display a command bar
 * Restore a built-in command bar
 * Menus
 * Add a custom menu control to a command bar
 * Disable a menu control on a command bar
 * Enable a menu control on a command bar
 * Delete a menu control on a command bar
 * Restore a menu control on a command bar
 * Commands
 * Add a separator bar to a menu control
 * Create a custom command control on a menu
 * Put a check mark next to a command control
 * Disable a command control on a command Bar
 * Enable a command control on a command Bar
 * Delete a command control on a menu
 * Restore a built-in command control on a menu
 * Submenus
 * Add a submenu
 * Add a command to a submenu
 * Disable a command control on a submenu
 * Delete a command on a submenu
 * Disable a submenu control
 * Delete a submenu control
 * Shortcut menu bars
 * Create a new shortcut menu bar
 * Shortcut menus
 * Create a command on a shortcut menu bar
 * Disable a command control on a shortcut menu bar
 * Delete a command on a shortcut menu bar
 * Delete a shortcut menu bar
 * Restore a command on a built-in shortcut menu bar
 * Submenus on shortcut menus
 * Create a new submenu on a shortcut menu bar
 * Create a command control on a submenu located on a shortcut menu bar
 * Disable a submenu item control on a shortcut menu
 * Delete a submenu item control on a shortcut menu
 * Disable a submenu control on a shortcut menu
 * Delete a submenu control on a shortcut menu
 * Find more information
 * Object Browser
 * Microsoft Knowledge Base



INTRODUCTION
To perform many of the common tasks that are associated with customizing menu bars and menus in Microsoft Excel 2000, in Microsoft Excel 2002, and in Microsoft Office Excel 2003, use the Customize dialog box. To perform more advanced tasks, or to tailor menu bars and menus for a custom program, you may want to create Microsoft Visual Basic for Applications (VBA) codes.

For more information about how to use the Customize dialog box, click Microsoft Excel Help on the Help menu, type customize menu bar in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

This article can help you learn techniques for writing VBA code for customizing menu bars, menus, menu items, submenus, and shortcut menus.

back to the top



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.

Examples in this article
The examples in this article use VBA code in Excel 2000, in Excel 2002, and in Excel 2003 to customize menus. To use the example macros, follow these steps:
 * 1) Start Excel.
 * 2) On the Tools menu, point to Macro, and then click Visual Basic Editor.
 * 3) On the Insert menu, click Module.
 * 4) Type one of the macro examples from this article, or you can use a copy-and-paste operation to copy the macro examples to a module sheet.
 * 5) On the Run menu, click Run Sub/User Form.
 * 6) If the Macro dialog box appears, click the macro name, and then click Run.

back to the top

Command bars
In Microsoft Office, toolbars, menu bars, and shortcut menus are all controlled programmatically as one type of object: command bars. All the following items are represented in VBA by CommandBar objects:
 * Menu bars, toolbars, and shortcut menus.
 * Menus on menu bars and toolbars.
 * Submenus on menus, submenus, and shortcut menus.

You can modify any built-in menu bar or any built-in toolbar, and you can create and modify custom toolbars, menu bars, and shortcut menus to deliver with your VBA code. You present the features of your program as individual buttons on toolbars or as groups of command names on menus. Because toolbars and menus are both command bars, you use the same kind of controls.

In VBA and in Microsoft Visual Basic, buttons and menu items are represented by CommandBarButton objects. The pop-up controls that display menus and submenus are represented by CommandBarPopup objects. In the following examples, the control that is named &quot;Menu&quot; and the control that is named &quot;Submenu&quot; are both pop-up controls that display a menu and a submenu. Both the menu and the submenu are unique CommandBar objects with their own set of controls.

In Microsoft Excel, menu bars and toolbars are referred to as the same programmable object type, the CommandBar object. You use the controls in the CommandBar object to refer to menus, menu items, submenus, and shortcut menus. You use a constant with each control in the Type argument to specify the type of control that you want to use for the menu, the submenu, or the command.

back to the top

Control constants
The following is a list of the various control constants in Excel 2003 that specify the type of graphical control to use for a particular menu bar control:
 * MsoControlActiveX*
 * MsoControlAutoCompleteCombo***
 * MsoControlButton
 * MsoControlButtonDropdown
 * MsoControlButtonPopup
 * MsoControlComboBox
 * MsoControlCustom
 * MsoControlDropdown
 * MsoControlEdit
 * MsoControlExpandingGrid
 * MsoControlGauge
 * MsoControlGenericDropdown
 * MsoControlGraphicCombo
 * MsoControlGraphicDropdown
 * MsoControlGraphicPopup
 * MsoControlGrid
 * MsoControlLabel
 * MsoControlLabelEx***
 * MsoControlOCXDropDown
 * MsoControlPane **
 * MsoControlPopup
 * MsoControlSpinner***
 * MsoControlSplitButtonMRUPopup
 * MsoControlSplitButtonPopup
 * MsoControlSplitDropdown
 * MsoControlSplitExpandingGrid
 * MsoControlWorkPane**


 * =New in Microsoft Excel 2000


 * = New in Microsoft Excel 2002


 * =New in Microsoft Office Excel 2003

back to the top

Menu bars
A menu bar is a kind of command bar. A menu bar is the kind of object where you add menus, menu items, and submenus.

For more information about how to manage menu bars and menu items in Excel, follow these steps:
 * 1) Start the Microsoft Visual Basic Editor.
 * 2) On the Help menu, click Microsoft Visual Basic Help.
 * 3) In the Office Assistant box or in the Answer Wizard box, type Menu bars, and then click Search.
 * 4) In Excel 2003 and in Excel 2002, click Adding and Managing Menu Bars and Menu Items. In Excel 2000, click About menus and toolbars.

You can make modifications to both the menu bar and to the controls on that menu bar at run time. The changes that you make to the menu bar may affect the appearance or the position of the menu bar. Changes that you make to the controls depend on the control type. The following table lists the most common properties and the common methods for changing the state, the action, or the contents of a control:

back to the top

Return an ID for a command bar control
The following example code returns the ID for the active menu bar:

Sub Id_Control Dim myId as Object set myId = CommandBars(&quot;Worksheet Menu Bar&quot;).Controls(&quot;Tools&quot;) MsgBox myId.Caption & Chr(13) & MyId.Id End Sub

back to the top

Determine the name of the active menu bar
The following example code returns the name for the active menu bar:

Sub MenuBars_GetName MsgBox CommandBars.ActiveMenuBar.Name End Sub

back to the top

Save the active state (for built-in or for customized menu bars)
You may want to declare the OriginalMenuBar variable a public variable so that a subroutine can use it in another subroutine, such as an Auto_Close subroutine. Declaring and using the variable this way resets the user's previous menu bar to its original state. The following sample macro resets the menu bar:

Public OriginalMenuBar as Object

Sub MenuBars_Capture Set OriginalMenuBar = CommandBars.ActiveMenuBar End Sub

back to the top

Create a custom command bar
The following example code creates a custom command bar that is named My Command Bar :

Sub MenuBar_Create Application.CommandBars.Add Name:=&quot;My command bar&quot; End Sub

You can also create a custom command bar by using the Temporary:=True argument. The Temporary:=True argument permits the command bars to automatically be reset when you quit Excel. The following code uses the Temporary:=True argument to create a custom command bar: Sub MenuBar_Create Application.CommandBars.Add Name:=&quot;My command bar&quot;, Temporary:=True End Sub back to the top

Display a custom command bar
The following example creates and displays a custom My Custom Bar menu bar, and then replaces the built-in menu bar:

Sub MenuBar_Show Dim myNewBar As Object Set myNewBar = CommandBars.Add(Name:=&quot;Custom1&quot;, Position:=msoBarFloating) ' You must first enable your custom menu bar before you make it visible. ' Enabling a menu bar adds it to the list of available menu bars on  ' the Customize dialog box. ' Setting the menubar property to True replaces the built-in menu bar. myNewBar.Enabled = True myNewBar.Visible = True End Sub

back to the top

Delete a custom command bar
The following example code deletes the custom menu bar that is named Custom 1:

Sub MenuBar_Delete CommandBars(&quot;Custom1&quot;).Delete End Sub

back to the top

Hide a command bar
The following example code removes the built-in Chart menu bar from the list of available menu bars: Sub MenuBar_Display CommandBars(&quot;Chart&quot;).Enabled = False End Sub

back to the top

Display a command bar
The following example code adds the built-in Chart menu bar from the list of available menu bars:

Sub MenuBar_Display CommandBars(&quot;Chart&quot;).Enabled = True End Sub

back to the top

Restore a built-in command bar
Restoring a menu bar resets the default controls (for both menus and menu items). The following example code restores the built-in Chart menu bar:

Sub MenuBar_Restore CommandBars(&quot;Chart&quot;).Reset End Sub Note You can only reset built-in menu bars. You cannot reset a custom menu bar.

back to the top

Menus
Restoring a menu bar resets the default controls (for both menus and menu items). The following example code restores the built-in Chart menu bar: Sub MenuBar_Restore CommandBars(&quot;Chart&quot;).Reset End Sub Note You can only reset built-in menu bars. You cannot reset a custom menu bar.

back to the top

Add a custom menu control to a command bar
The following example code adds the name of a menu that you add programmatically to the Worksheet menu bar. For example, this code adds the menu name New Menu to the to the Worksheet menu bar.

Note You can give this menu any name that you want.

Sub Menu_Create Dim myMnu As Object Set myMnu = CommandBars(&quot;Worksheet menu bar&quot;).Controls. _     Add(Type:=msoControlPopup, before:=3) With myMnu ' The &quot;&&quot; denotes a shortcut key assignment (Alt+M in this case). .Caption = &quot;New &Menu&quot; End With End Sub

back to the top

Disable a menu control on a command bar
A menu control that is disabled appears dimmed and is not available on a command bar. The following example disables the New Menu menu:

Sub Menu_Disable CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;New &Menu&quot;).Enabled = False End Sub

back to the top

Enable a menu control on a command bar
The following example code enables the New Menu menu that you disabled in the &quot;Disable a menu control on a command bar&quot; section:

Sub Menu_Disable CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;New &Menu&quot;).Enabled = True End Sub

back to the top

Delete a menu control on a command bar
The following code example deletes the New Menu menu that you created in the &quot;Add a custom menu control to a command bar&quot; section from the Worksheet menu bar:

Sub Menu_Delete CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;New &Menu&quot;).Delete End Sub

back to the top

Restore a menu control on a command bar
The following example code restores the built-in Chart menu bar on the Worksheet menu bar:

Sub Menu_Restore Dim myMnu As Object Set myMnu = CommandBars(&quot;Chart&quot;) myMnu.Reset End Sub

back to the top

Commands
The range of modifications that you can make to a command depends on the control type. Generally, buttons are either enabled or are hidden. Edit boxes, drop-down list boxes, and combo boxes are more versatile in that you can add or delete items from the list. Additionally, you can determine the action that is performed by looking at the value of the items that you selected from the list. You can change the action of any control to a built-in function or to a custom function.

The following table lists the most common properties of a control and the methods for changing the state, the action, or the contents of a control:

For more information about menus in Excel 2003 and in Excel 2002, follow these steps:
 * 1) Start the Visual Basic Script Editor.
 * 2) On the Help menu, click Microsoft Visual Basic Help.
 * 3) In the Search Help box, type menus, and then press ENTER.
 * 4) Click Adding and Managing Menu Bars and Menu Items (Office).

back to the top

Add a separator bar to a menu control
The following example code adds a separator bar before the Worksheet command on the Insert menu:

Sub menuItem_AddSeparator CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Insert&quot;) _ .Controls(&quot;Worksheet&quot;).BeginGroup = True End Sub Note To remove a separator bar, set the BeginGroup property to False.

back to the top

Create a custom command control on a menu
The following example code creates a new command that is named Custom1 on the Tools menu of the Worksheet menu bar, and then runs the Code_Custom1 macro when you click Custom1:

Sub menuItem_Create With CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) .Controls.Add(Type:=msoControlButton, Before:=1).Caption = &quot;Custom1&quot; .Controls(&quot;Custom1&quot;).OnAction = &quot;Code_Custom1&quot; End With End Sub

back to the top

Put a check mark next to a command control
The following example code puts a check mark next to the Custom1 command if it is not selected, and then removes the check mark if the Custom1 command is selected:

Sub menuItem_checkMark Dim myPopup as Object

Set myPopup = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) If myPopup.Controls(&quot;Custom1&quot;).State = msoButtonDown Then ' Remove check mark next to menu item. myPopup.Controls(&quot;Custom1&quot;).State = msoButtonUp MsgBox &quot;Custom1 is now unchecked&quot; Else ' Add check mark next to menu item. myPopup.Controls(&quot;Custom1&quot;).State = msoButtonDown MsgBox &quot;Custom1 is now checked&quot; End If End Sub

back to the top

Disable a command control on a command bar
The following example code disables the Custom1 command that you created on the Tools menu in the &quot;Create a custom command control on a menu&quot; section:

Sub MenuItem_Disable Dim myCmd as Object Set myCmd = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) myCmd.Controls(&quot;Custom1&quot;).Enabled = False End Sub

back to the top

Enable a command control on a command bar
The following example code enables the Custom1 command that you disabled in the &quot;Disable a command control on a command bar&quot; section:

Sub MenuItem_Enable Dim myCmd as Object Set myCmd = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) myCmd.Controls(&quot;Custom1&quot;).Enabled = True End Sub

back to the top

Delete a command control on a menu
The following example code deletes the Save command on the File menu:

Sub menuItem_Delete Dim myCmd As Object Set myCmd = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;File&quot;) myCmd.Controls(&quot;Save&quot;).Delete End Sub

back to the top

Restore a built-in command control on a menu
To restore a command control on a menu, you must know the identification (ID) number for the control. To determine the ID number, see the &quot;Return an ID for a command bar control&quot; section. The following example deletes and then restores the Save command that you deleted in the &quot;Delete a command control on a menu&quot; section:

Sub menuItem_Restore Dim myCmd As Object Set myCmd = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;File&quot;) ' Id 3 refers to the Save menu item control. myCmd.Controls.Add Type:=msoControlButton, ID:=3, Before:=5 End Sub

back to the top

Submenus
Submenus appear to the side of the parent menu when you click a command. A command that is a submenu control has a small black arrow that is located at the right end of the command name.

back to the top

Add a submenu
The following example code adds a new submenu that is named NewSub to the Tools menu on the Worksheet menu bar:

Sub SubMenu_Create Dim newSub as Object Set newSub = CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) With newSub .Controls.Add(Type:=msoControlPopup, Before:=1).Caption=&quot;NewSub&quot; End With End Sub

back to the top

Add a command to a submenu
The following example code adds a new command that is named SubItem1 to the NewSub submenu, and then it runs the Code_SubItem1 macro when you click SubItem1:

Sub SubMenu_AddItem Dim newSubItem as Object Set newSubItem = CommandBars(&quot;Worksheet menu bar&quot;) _ .Controls(&quot;Tools&quot;).Controls(&quot;NewSub&quot;) With newSubItem .Controls.Add(Type:=msoControlButton, Before:=1).Caption = &quot;SubItem1&quot; .Controls(&quot;SubItem1&quot;).OnAction = &quot;Code_SubItem1&quot; End With End Sub

back to the top

Disable a command control on a submenu
The following example code disables the same SubItem command that you created in the &quot;Add a command to a submenu&quot; section : Sub SubMenu_DisableItem CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) _ .Controls(&quot;NewSub&quot;).Controls(&quot;SubItem1&quot;).Enabled = False End Sub

The following example enables the same SubItem command: Sub SubMenu_DisableItem CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) _ .Controls(&quot;NewSub&quot;).Controls(&quot;SubItem1&quot;).Enabled = True End Sub

back to the top

Delete a command on a submenu
The following example deletes the SubItem1 command that you created on the NewSub submenu in the &quot;Add a command to a submenu&quot; section:

Sub SubMenu_DeleteItem CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) _ .Controls(&quot;NewSub&quot;).Controls(&quot;SubItem1&quot;).Delete End Sub

back to the top

Disable a submenu control
The following example code disables the NewSub submenu that you created on the Tools menu in the &quot;Add a submenu&quot; section:

Sub SubMenu_DisableSub CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) _ .Controls(&quot;NewSub&quot;).Enabled = False End Sub Note To enable the disabled control, set the Enabled property to True.

back to the top

Delete a submenu control
The following example code deletes the NewSub submenu that you created on the Tools menu in the &quot;Add a submenu&quot; section:

Sub SubMenu_DeleteSub CommandBars(&quot;Worksheet menu bar&quot;).Controls(&quot;Tools&quot;) _ .Controls(&quot;NewSub&quot;).Delete End Sub

back to the top

Shortcut menu bars
A shortcut menu is a floating command bar that appears when the user right-clicks an object. A shortcut menu bar can contain the same control types as a command bar and the controls behave the same as the controls on a command bar. You cannot create or modify shortcut menus from the program's interface in most programs. Therefore, you must create and modify your shortcut menus at run time.

For more information about shortcut menus in Excel 2002 and in Excel 2003, follow these steps:
 * 1) Start the Visual Basic Script Editor.
 * 2) On the Help menu, click Microsoft Visual Basic Help.
 * 3) In the Search Help box, type shortcut, and then press ENTER.
 * 4) Click Adding and Displaying Shortcut Menus.

back to the top

Create a new shortcut menu bar
The following example code creates a new shortcut menu bar that is named myShortcutBar:

Sub Shortcut_Create Dim myShtCtBar as Object Set myShtCtBar = CommandBars.Add(Name:=&quot;myShortcutBar&quot;, _  Position:=msoBarPopup) ‘ This displays the shortcut menu bar. ‘ 200, 200 refers to the screen position in pixels as x and y coordinates. myShtCtBar.ShowPopup 200,200 End Sub Note The shortcut menu bar appears empty because no controls (menu items or submenus) have been added to it.

back to the top

Shortcut menus
Shortcut menu bars appear when you use the right mouse button to click a specific Excel object. Excel has many shortcut menu bars for which a variety of menus are available. You can also create custom shortcut menu bars and customize the built-in menu bars.

back to the top

Create a command on a shortcut menu bar
The following example code creates a new menu command that is named Item1 on the myShortcutBar shortcut menu bar and it runs the Code_Item1 macro when you click Item1:

Sub Shortcut_AddItem Dim myBar as Object Set myBar = CommandBars(&quot;myShortcutBar&quot;) With myBar .Controls.Add (Type:=msoControlButton, before:=1).Caption = &quot;Item1&quot; .Controls(&quot;Item1&quot;).OnAction = &quot;Code_Item1&quot; End With myBar.ShowPopup 200,200 End Sub

back to the top

Disable a command control on a shortcut menu bar
The following example code disables the Item1 command that you created in the &quot;Create a command on a shortcut menu&quot; section:

Sub Shortcut_DisableItem Set myBar = CommandBars(&quot;myShortcutBar&quot;) myBar.Controls(&quot;Item1&quot;).Enabled = False myBar.ShowPopup 200,200 End Sub Note To enable the disabled item, set the Enabled property to True.

back to the top

Delete a command on a shortcut menu bar
The following example code deletes the menu command that is named Item1 on the myShortcutBar shortcut menu bar:

Sub Shortcut_DeleteItem Set myBar = CommandBars(&quot;myShortcutBar&quot;) myBar.Controls(&quot;Item1&quot;).Delete myBar.ShowPopup 200,200 End Sub

back to the top

Delete a shortcut menu bar
Deleting the shortcut menu bar removes all the items. You cannot restore a deleted custom menu bar. To restore it, you must re-create it and all the menu items and the submenus.

The following example code deletes the myShortCutBar shortcut menu bar that you created in the &quot;Create a command on a shortcut menu bar&quot; section:

Sub Shortcut_DeleteShortCutBar CommandBars(&quot;MyShortCutBar&quot;).Delete End Sub

back to the top

Restore a command on a built-in shortcut menu bar
The following example code restores the default commands on the worksheet Cell shortcut menu bar:

Sub Shortcut_RestoreItem CommandBars(&quot;Cell&quot;).Reset End Sub

back to the top

Submenus on shortcut menus
You can create submenus on shortcut menu bars. Submenus appear to the side of the parent menu when you click a command control. A command that is a submenu control has a small, black arrow that is located to the right of its name.

back to the top

Create a new submenu on a shortcut menu bar
The following example adds a new submenu that is named NewSub on the worksheet Cell shortcut menu:

Sub ShortcutSub_Create CommandBars(&quot;Cell&quot;).Controls.Add(Type:=msoControlPopup, before:=1) _ .Caption = &quot;NewSub&quot; ' This displays the shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates. CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub Note The submenu is empty because no menu items have been added to it.

back to the top

Create a command control on a submenu that is located on a shortcut menu bar
The following macro adds the SubItem1 command to the submenu NewSub that you created on the Cell shortcut menu, and then runs the Code_SubItem1 macro when you click SubItem1:

Sub ShortcutSub_AddItem Dim newSubItem as Object Set newSubItem = CommandBars(&quot;Cell&quot;).Controls(&quot;NewSub”) With newSubItem .Controls.Add(Type:=msoControlButton, before:=1).Caption = &quot;subItem1&quot; ' This will run the subItem1_Code macro when subItem1 is clicked. .Controls(&quot;subItem1&quot;).OnAction = &quot;Code_subItem1&quot; End With ' This displays the Cell shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub

back to the top

Disable a submenu item control on a shortcut menu
The following example code disables the SubItem1 command on the NewSub submenu:

Sub ShortcutSub_DisableItem CommandBars(&quot;Cell&quot;).Controls(&quot;NewSub&quot;) _ .Controls(&quot;subItem1&quot;).Enabled = False ' This displays the Cell shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates. CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub Note To enable a disabled item, set the Enabled property to True.

back to the top

Delete a submenu item control on a shortcut menu
The following example deletes the SubItem1 command on the NewSub submenu:

Sub ShortcutSub_DeleteItem CommandBars(&quot;Cell&quot;).Controls(&quot;NewSub&quot;).Controls(&quot;subItem1&quot;).Delete ' This displays the Cell shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates. CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub

back to the top

Disable a submenu control on a shortcut menu
The following example code disables the NewSub submenu on the Cell shortcut menu bar:

Sub ShortcutSub_DisableSub CommandBars(&quot;Cell&quot;).Controls(&quot;NewSub&quot;).Enabled = False ' This displays the Cell shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates. CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub Note To enable a disabled item, set the Enabled property to True.

back to the top

Delete a submenu control on a shortcut menu
The following example code deletes the NewSub submenu that you created on the Cell shortcut menu bar:

Sub ShortcutSub_DeleteSub CommandBars(&quot;Cell&quot;).Controls(&quot;NewSub&quot;).Delete ' This displays the Cell shortcut menu bar. ' 200, 200 refers to the screen position in pixels as x and y coordinates. CommandBars(&quot;Cell&quot;).ShowPopup 200, 200 End Sub

back to the top

Find more information
The following resources are available to provide more information about how to customize menus and menu bars in Excel.

back to the top

Object Browser
The Object Browser contains a complete list of all the properties and all the methods for a specific command. To find this information, switch to the Visual Basic Editor (press ALT+F11), click Object Browser on the View menu (or press F2), type the name of the control in the Search box, and then press ENTER or click Search.

back to the top

Microsoft Knowledge Base
The Microsoft Knowledge Base is a primary Microsoft product information source for Microsoft Product Support Services support professionals. The Microsoft Knowledge Base is also available to Microsoft customers. This comprehensive database contains detailed articles with technical information about Microsoft products, documented fix lists, documentation errors, and answers to frequently asked technical support questions.

To connect to the Microsoft Knowledge Base, visit the following Microsoft Web site, and then follow the instructions that appear on the page:

http://support.microsoft.com

back to the top

Keywords: kbhowtomaster KB830502

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.